Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
What is the easiest way to get to this without having to write several load steps and IF conditions for each code?
CODE_1_DATE is the date CODE_1 is placed, similarly for other codes
If an Account has two same codes, like 789 below, it has 120 in code_1 and code_2, I would need the 120 that is places latest. so it would be 1/9/2019
In the actual data I have as many as 12-15 codes.
HAVE:
ACCOUNT | CODE_1 | CODE_2 | CODE_3 | code_4 | CODE_1_DATE | CODE_2_DATE | CODE_3_DATE | CODE_4_DATE |
123 | 120 | 121 | 122 | 1/2/2019 | 1/3/2019 | 1/4/2019 | ||
456 | 120 | 121 | 122 | 123 | 1/20/2019 | 1/3/2019 | 1/5/2019 | 1/8/2019 |
789 | 120 | 120 | 122 | 121 | 1/8/2019 | 1/9/2019 | 1/10/2019 | 1/15/2019 |
WANT:
ACCOUNT | CODE_120 | CODE_120_DATE | CODE_121 | CODE_121_DATE | CODE_122 | CODE_122_DATE | CODE_123 | CODE_123_DATE |
123 | Y | 1/2/2019 | Y | 1/3/2019 | Y | 1/4/2019 | ||
456 | Y | 1/20/2019 | Y | 1/3/2019 | Y | 1/5/2019 | Y | 1/8/2019 |
789 | Y | 1/9/2019 | Y | 1/15/2019 | Y | 1/10/2019 |
I doubt that I would try to solve this and other tasks with your displayed data-structure else I would transform it with two joined crosstable-loads - means to load code and code-date separate and joining them together:
After it I'm not sure that I would flag everything within the datamodel else I could imagine that you could fetch your needed values with firstsortedvalue() directly within the UI. If it should really happens within the script you might also use min/max/firstsortedvalue in separate loadings and just keeping them as associated tables or maybe re-joining them to the resident table.
An alternatively to the aggregation approach might be to use interrecord-functions within a sorted load to access and compare the previous loaded records:
- Marcus