Help with Logic

[OmarBenSalem]

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.

ID	Date1	Date2	Date3	Rank
1	17/06/2017	15/01/2018	15/11/2016	Date3 - Date1- Date2

[sunny_talwar]

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

[OmarBenSalem]

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?

[marcus_sommer]

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

[sunny_talwar]

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

Table:

LOAD * INLINE [

    ID, Date1, Date2, Date3

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

];

CrossTable:

CrossTable (DateType, Date)

LOAD *

Resident Table;

Left Join (Table)

LOAD ID,

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

Resident CrossTable

Group By ID;

Drop Table CrossTable;

[sunny_talwar]

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

Table:

LOAD * INLINE [

    ID, Date1, Date2, Date3

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

];

CrossTable:

CrossTable (DateType, Date)

LOAD *

Resident Table;

Left Join (Table)

LOAD ID,

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

Resident CrossTable

Group By ID;

Drop Table CrossTable;

[sunny_talwar]

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

[marcus_sommer]

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

[OmarBenSalem]

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

[sunny_talwar]

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