Parsing dates

Support/help with CloverETL (4.9) and CloverDX (5.0 or newer) implementation problems

hesske
Posts: 15
Joined: Wed Jan 03, 2018 7:31 pm

Parsing dates

Postby hesske » Fri Sep 21, 2018 2:47 pm

I am trying to build an array(list) of dates between a start date and end date. I have been trying it this way in CTL2:
date[] dates;
if (dateDiff($in.0.end_date, $in.0.start_date, day) >1){
for (date i=dateAdd($in.0.start_date, 1, day); i<$in.0.end_date; dateAdd(i, 1, day)){
append(dates, i);
}
}

This keeps getting the graph stuck until my Java heap memory max is reached.
Please point out my mistake and suggest an alternative approach.
Thanks

hesske
Posts: 15
Joined: Wed Jan 03, 2018 7:31 pm

Re: Parsing dates

Postby hesske » Sat Sep 22, 2018 12:36 pm

Forgot to include this part: $out.0.dates = dates;

bartonv
Posts: 125
Joined: Wed May 03, 2017 12:10 pm

Re: Parsing dates

Postby bartonv » Thu Sep 27, 2018 2:49 pm

Hi Hesske,
attached is an example of how this task can be approached. Worth noting is the following:
  • I am generating a random set of dates where 'date2' is always after 'date1' (to simplify the example)
  • each pair of dates is assigned with an id (sequenced)
  • in the Normalizer component, I have used the dateDiff() function in order to return the total number of dates that fall into the date range (the +2 represents including both dates)
  • then, I am using the dateAdd() function in order to generate the dates within the loop of the transform() function so ultimately, I am outputting all the individual dates, each one assigned with the respective id of the original date range
  • in the Denormalizer component, I am simply putting all the dates back together into an array field by using the id as the group key.
Kind regards,
Attachments
parsingDates.grf
(6.96 KiB) Downloaded 48 times
---
Vladimir Barton
CloverCARE Support
CloverDX

Visit us online at http://www.cloverdx.com

hesske
Posts: 15
Joined: Wed Jan 03, 2018 7:31 pm

Re: Parsing dates

Postby hesske » Thu Sep 27, 2018 6:05 pm

Thank you Vladimir. I will adapt what you gave me to meet what I need. Thanks again!

hesske
Posts: 15
Joined: Wed Jan 03, 2018 7:31 pm

Re: Parsing dates

Postby hesske » Mon Oct 01, 2018 2:14 pm

Thanks again for the graph Vladimir! I do have a follow-up question. It is using the array of dates created, but is a slightly different topic, so please let me know if it would be better to start a new thread for it. Here is the issue:
I am now trying to take each date from the array(list) created and compare it with a set of dates, starting with the current date and going out 30 days. I have created parameters for each of the 30 days as seen in the attachment.
hpw date parameters.JPG
hpw date parameters.JPG (50.18 KiB) Viewed 1225 times

I use these parameters to match labels used for metadata as well.

I was hoping to use a switch statement to compare a date from the list against each day, and if it is a match, put a string of "U" in that field with the matching metadata.
Here is how I was wanting to do it for each of the 30 days:
do {
switch (poll($in.0.Dates)) {
case str2date(${TODAY}, "yyyy-MM-dd"): $out.0.Today = "U";
break;
default: "";
}

}while (length($in.0.Dates) > 0);

return ALL;
}
However it appears the switch statement will only allow literals, but I am not wanting to put a case for every day of the year. Is there another way to programmatically, or via components, to make this comparison and output the string on the matching metadata field?

Thanks

bartonv
Posts: 125
Joined: Wed May 03, 2017 12:10 pm

Re: Parsing dates

Postby bartonv » Mon Oct 08, 2018 1:20 pm

Hello Hesske,
unfortunately, I am unable to download the attached image. Nevertheless, I believe there is a more straightforward option of how to achieve the desired result. If I understood your request correctly, what you are trying to obtain would be the following:

Let's say this is the initial set of dates in a given date range:

Code: Select all

2018-10-06
2018-10-07
2018-10-08
2018-10-09
2018-10-10

Given the today's date 2018-10-08, the desired output would be this:

Code: Select all

2018-10-06
2018-10-07
U
U
U

Is that correct? If so, please find the attached graph (a modified version of the previous example) that demonstrates the usage of the ExtHashJoin component to achieve this. The main idea is to generate those 30 dates within the graph directly (instead of using parameters) and use them on the slave port of ExtHashJoin. Then, CloverDX would try to find a matching slave record for every incoming date on the master port. If it succeeds, it will output the "U" string instead of the date.
If this example does not address your question optimally feel free to reach out back to us.
Kind regards,
Vladi
Attachments
parsingDates2.grf
(10.47 KiB) Downloaded 59 times
---
Vladimir Barton
CloverCARE Support
CloverDX

Visit us online at http://www.cloverdx.com

hesske
Posts: 15
Joined: Wed Jan 03, 2018 7:31 pm

Re: Parsing dates

Postby hesske » Mon Oct 08, 2018 6:09 pm

Thank you Vladimir. I found a way that produced what I am looking for by adding these lines to my Reformat code and declaring variables and not using the parameters in the code:

while (length($in.0.Dates) > 0){
string today = date2str(today(), "M/dd");
string day2 = date2str(dateAdd(today(),1,day), "M/dd");
string day3 = date2str(dateAdd(today(),2,day), "M/dd");
string day4 = date2str(dateAdd(today(),3,day), "M/dd");
string day5 = date2str(dateAdd(today(),4,day), "M/dd");
string day6 = date2str(dateAdd(today(),5,day), "M/dd");
string day7 = date2str(dateAdd(today(),6,day), "M/dd");
string day8 = date2str(dateAdd(today(),7,day), "M/dd");
string day9 = date2str(dateAdd(today(),8,day), "M/dd");
string day10 = date2str(dateAdd(today(),9,day), "M/dd");
string day11 = date2str(dateAdd(today(),10,day), "M/dd");
string day12 = date2str(dateAdd(today(),11,day), "M/dd");
string day13 = date2str(dateAdd(today(),12,day), "M/dd");
string day14 = date2str(dateAdd(today(),13,day), "M/dd");
string day15 = date2str(dateAdd(today(),14,day), "M/dd");
string day16 = date2str(dateAdd(today(),15,day), "M/dd");
string day17 = date2str(dateAdd(today(),16,day), "M/dd");
string day18 = date2str(dateAdd(today(),17,day), "M/dd");
string day19 = date2str(dateAdd(today(),18,day), "M/dd");
string day20 = date2str(dateAdd(today(),19,day), "M/dd");
string day21 = date2str(dateAdd(today(),20,day), "M/dd");
string day22 = date2str(dateAdd(today(),21,day), "M/dd");
string day23 = date2str(dateAdd(today(),22,day), "M/dd");
string day24 = date2str(dateAdd(today(),23,day), "M/dd");
string day25 = date2str(dateAdd(today(),24,day), "M/dd");
string day26 = date2str(dateAdd(today(),25,day), "M/dd");
string day27 = date2str(dateAdd(today(),26,day), "M/dd");
string day28 = date2str(dateAdd(today(),27,day), "M/dd");
string day29 = date2str(dateAdd(today(),28,day), "M/dd");
string day30 = date2str(dateAdd(today(),29,day), "M/dd");
string a = date2str(poll($in.0.Dates),"M/dd");
if (a == today) {
$out.0.Today = "U";
}
if (a == day2) {
$out.0.Day2 = "U";
}
if (a == day3) {
$out.0.Day3 = "U";
}
if (a == day4) {
$out.0.Day4 = "U";
}
if (a == day5) {
$out.0.Day5 = "U";
}
if (a == day6) {
$out.0.Day6 = "U";
}
if (a == day7) {
$out.0.Day7 = "U";
}
if (a == day8) {
$out.0.Day8 = "U";
}
if (a == day9) {
$out.0.Day9 = "U";
}
if (a == day10) {
$out.0.Day10 = "U";
}
if (a == day11) {
$out.0.Day11 = "U";
}
if (a == day12) {
$out.0.Day12 = "U";
}
if (a == day13) {
$out.0.Day13 = "U";
}
if (a == day14) {
$out.0.Day14 = "U";
}
if (a == day15) {
$out.0.Day15 = "U";
}
if (a == day16) {
$out.0.Day16 = "U";
}
if (a == day17) {
$out.0.Day17 = "U";
}
if (a == day18) {
$out.0.Day18 = "U";
}
if (a == day19) {
$out.0.Day19 = "U";
}
if (a == day20) {
$out.0.Day20 = "U";
}
if (a == day21) {
$out.0.Day21 = "U";
}
if (a == day22) {
$out.0.Day22 = "U";
}
if (a == day23) {
$out.0.Day23 = "U";
}
if (a == day24) {
$out.0.Day24 = "U";
}
if (a == day25) {
$out.0.Day25 = "U";
}
if (a == day26) {
$out.0.Day26 = "U";
}
if (a == day27) {
$out.0.Day27 = "U";
}
if (a == day28) {
$out.0.Day28 = "U";
}
if (a == day29) {
$out.0.Day29 = "U";
}
if (a == day30) {
$out.0.Day30 = "U";
}


}


Thanks again

bartonv
Posts: 125
Joined: Wed May 03, 2017 12:10 pm

Re: Parsing dates

Postby bartonv » Tue Oct 09, 2018 8:16 am

Hi Hesske,
thank you for the heads up! I am glad a working solution was found.
---
Vladimir Barton
CloverCARE Support
CloverDX

Visit us online at http://www.cloverdx.com