18 Replies Latest reply: Jul 20, 2018 10:01 AM by omar bensalem

# Help with Logic

Hi all ( stalwar1)

I have a question:

I have sthing like this:

Date1,2 and 3; and for every ID, I want to create a rank field, to rank the dates in ascending order; is there a way to do this? without long if else..?

and one remark: NOT ALL DATES are always there; u can find many IDs with one or many missing dates.

IDDate1Date2Date3Rank
117/06/201715/01/201815/11/2016Date3 - Date1- Date2
• ###### Re: Help with Logic

May be cross table and then Concatenate them back with sort weight of Date

• ###### Re: Help with Logic

The idea is to have sthing like this :

where we track the chrnological order of the actions based on their Starting Date:

if I select Action 1 : Publication:

I'll have this: I'll know those who had as first action: publication , what was their second one, action 3 and so on..

But to achieve this; I've had a HUGE if else script: H.U.G.E;

it works just fine; BUT, if i add yet another action in my logic; ALL THE SCRIPT must change with so MANY combinations.. I just don't want to think about it ...

Do u see any easier way to achieve this sunny?

• ###### Re: Help with Logic

If you used a subfield-load after the crosstable-concat suggestion from Sunny you would get your Rank field. Maybe easier than that would be to use range-functions like:

rangemin(Date1, Date2, Date3) & '-' &

rangeavg(Date1, Date2, Date3) & '-' &

rangemax(Date1, Date2, Date3) as Rank

- Marcus

• ###### Re: Help with Logic

Will RangeAvg() work here? I have a feeling it might not... but may be I am wrong.... I would have thought that may be RangeFractile will make more sense...

Update... something like this

Date(RangeMin(Date1, Date2, Date3)) & '-' &

Date(RangeFractile(0.5, Date1, Date2, Date3)) & '-' &

Date(RangeMax(Date1, Date2, Date3)) as Rank1

• ###### Re: Help with Logic

As far as there are only and always 3 different dates it should work. If not there might be some additionally measures necessary like checking the number of dates with rangecount() and branching then per if-then-else into different branches.

This means it will depend on the real data ... whereby I assume that even by multiple nested if-loops the performance is better as with the crosstable + aggregation load.

- Marcus

• ###### Re: Help with Logic

I don't know if you like this or not, but try this

Table:

ID, Date1, Date2, Date3

1, 17/06/2017, 15/01/2018, 15/11/2016

];

CrossTable:

CrossTable (DateType, Date)

Resident Table;

Left Join (Table)

Concat(DateType, ' - ', Date) as Rank

Resident CrossTable

Group By ID;

Drop Table CrossTable;

• ###### Re: Help with Logic

For concatenating the actual date... you can use this

Table:

ID, Date1, Date2, Date3

1, 17/06/2017, 15/01/2018, 15/11/2016

];

CrossTable:

CrossTable (DateType, Date)

Resident Table;

Left Join (Table)

Concat(Date, ' - ', Date) as Rank

Resident CrossTable

Group By ID;

Drop Table CrossTable;

• ###### Re: Help with Logic

Thanks to everyone ! I'll try ur suggestions tomorrow; but u'd have to know that I'd need a generic script that works whatever date I add; because right now, i have 4 not 3 dates and 2 more dates could be added..

thus, I don't think the avg() would work in this case

• ###### Re: Help with Logic

I think CrossTable might be the most generic solution you can get... but I would def. take into consideration Marcus's responses

• ###### Re: Help with Logic

The most generic way would be the suggestion from Sunny and if there are no serious performance impacts I would probably go with it.

But it could be also resolved on a record-level with a comparing from each value against each value. This may seem a quite heavy scripting with many if-loops but it could also be done more elegant - and I think the following could be even more optimized maybe by using some (parametrized) variables or a better algorithm - and you don't need to split it in respectively keep all intermediate steps:

t1:
*,
subfield(D, '|', index(RD, 1)) &'-'&
subfield(D, '|', index(RD, 2)) &'-'&
subfield(D, '|', index(RD, 3)) &'-'&
subfield(D, '|', index(RD, 4)) &'-'&
subfield(D, '|', index(RD, 5)) &'-'&
subfield(D, '|', index(RD, 6)) as Rank;
*,
(
rangesum(-(D1>D2),-(D1>D3),-(D1>D4),-(D1>D5),-(D1>D6))-6)*-1 as RD1,
(
rangesum(-(D2>D1),-(D2>D3),-(D2>D4),-(D2>D5),-(D2>D6))-6)*-1 as RD2,
(
rangesum(-(D3>D1),-(D3>D2),-(D3>D4),-(D3>D5),-(D3>D6))-6)*-1 as RD3,
(
rangesum(-(D4>D1),-(D4>D2),-(D4>D3),-(D4>D5),-(D4>D6))-6)*-1 as RD4,
(
rangesum(-(D5>D1),-(D5>D2),-(D5>D3),-(D5>D4),-(D5>D6))-6)*-1 as RD5,
(
rangesum(-(D6>D1),-(D6>D2),-(D6>D3),-(D6>D4),-(D6>D5))-6)*-1 as RD6,
(
rangesum(-(D1>D2),-(D1>D3),-(D1>D4),-(D1>D5),-(D1>D6))-6)*-1 &
(
rangesum(-(D2>D1),-(D2>D3),-(D2>D4),-(D2>D5),-(D2>D6))-6)*-1 &
(
rangesum(-(D3>D1),-(D3>D2),-(D3>D4),-(D3>D5),-(D3>D6))-6)*-1 &
(
rangesum(-(D4>D1),-(D4>D2),-(D4>D3),-(D4>D5),-(D4>D6))-6)*-1 &
(
rangesum(-(D5>D1),-(D5>D2),-(D5>D3),-(D5>D4),-(D5>D6))-6)*-1 &
(
rangesum(-(D6>D1),-(D6>D2),-(D6>D3),-(D6>D4),-(D6>D5))-6)*-1 as RD
;
load *, recno() as ID, D1&'|'&D2&'|'&D3&'|'&D4&'|'&D5&'|'&D6 as D inline [
D1   D2   D3   D4   D5   D6
26.04.2018 10.07.2017 12.09.2015 30.04.2017 12.04.2017 24.05.2016
02.09.2016 19.04.2016 24.07.2017 05.02.2018 10.03.2016 19.06.2018
15.09.2015 31.01.2016 17.01.2018 23.09.2015 03.09.2016 24.12.2017
30.01.2018 10.11.2017 31.07.2017 22.07.2016 27.05.2017 06.12.2015
19.09.2017 17.04.2017 21.04.2017 18.12.2016 11.07.2016 17.10.2016
28.01.2018 29.04.2016 09.03.2018 12.08.2017 17.04.2016 22.05.2016
18.06.2017 17.04.2017 29.01.2018 26.04.2018 04.02.2016 30.04.2017
17.11.2015 12.01.2016 28.10.2015 15.11.2016 18.09.2016 10.07.2018
07.01.2018 05.02.2018 12.03.2018 26.10.2016 19.03.2017 28.05.2018
11.01.2017 18.11.2016 17.09.2016 16.06.2018 13.09.2016 22.01.2016
09.07.2018 16.08.2015 15.11.2016 14.08.2016 26.03.2018 08.12.2017
02.02.2017 24.03.2016 15.02.2017 10.05.2017 19.07.2016 15.03.2017
07.08.2015 15.07.2018 29.11.2016 30.06.2018 26.06.2017 08.02.2018
10.07.2016 30.12.2015 10.04.2017 24.08.2015 26.12.2016 22.03.2016
27.05.2017 16.04.2018 01.09.2016 23.06.2018 24.04.2018 12.08.2017
17.11.2017 21.01.2017 06.12.2017 20.08.2016 16.09.2016 06.01.2018
06.01.2016 03.04.2016 11.12.2015 18.02.2017 16.08.2015 22.02.2018
20.04.2018 09.07.2016 28.11.2017 06.04.2016 08.12.2015 13.01.2017
12.09.2016 26.02.2017 30.01.2017 09.05.2017 14.02.2017 21.04.2016
01.09.2017 03.09.2016 22.04.2016 17.03.2018 22.11.2017 31.03.2016
]
(txt, delimiter is
\t);

- Marcus

• ###### Re: Help with Logic

This is awesome Marcus, thanks for sharing this. I guess the only issue I see with this is if that if somebody needs to add more date types, then there will be some work upfront to add the new date to the code in multiple places.

• ###### Re: Help with Logic

Yes, you are right - in this way the code is quite static and in some cases additionally efforts will be needed to get the approach more dynamically whereby the number of date-fields shouldn't be a big problem because they could be counted and within a loop the code could be created on the fly. More complicated would be cases with NULL's or missing/invalid values or in which any other exceptions needs to be handled ...

- Marcus

• ###### Re: Help with Logic

Thanks for this great work Marcus; I have a question though, would this work in case of NULL dates?

• ###### Re: Help with Logic

With some more manipulation this should work with NULL as well... but did you not like the CrossTable/Concat approach? I thought you wanted to something generic

• ###### Re: Help with Logic

I was busy all day long with meetings.. did not have the time to test anything sunny; I'll test it once I'd have time; thank u everyone !

• ###### Re: Help with Logic

In general I think it could. But it will need some additionally steps:

Here I removed 3 values - in D3 & D4 just a removing and in D2 I applied an alt(D2, 0) on the field. Only removing/cleaning the effects from the Rank field will be rather easy but if the ranking itself needs also to be adjusted in any way it might need some more efforts - so it will depend ...

- Marcus

• ###### Re: Help with Logic

Hi Omar,

RawData:

ID,Date1,Date2,Date3

1,17/06/2017,15/01/2018,15/11/2016

2,17/05/2017,15/08/2018,15/11/2019

];

temp:

CrossTable(DateType,Date,1)

left join (RawData)

Result:

load ID,Concat(DateType,'-',Date#(Date,'DD/MM/YYYY')) as Rank Resident temp group by ID;

drop table temp;

Hope it helps.