Help required

[OmarBenSalem]

Hi guys ( stalwar1‌  petter-s jontydkpi )

Would please help with the follow?

Suppose I have this:

load * inline [

id, Demande, Publication, Candidature,Creation

1, 15/01/2017, , 03/01/2017,10/12/2016

2, 12/03/2016, 15/01/2015, , 01/03/2014

3, 05/03/2017, 04/03/2017, 06/03/2017, 05/01/2015

];

What I want to achieve is :

id ,action0,action1,action2,action3

1, Creation, Candidature, Demande,

2, Creation, Publication, Demande,

3, Creation , Publication, Demande, Candidature

It's based on the Date

Here's what I did:

cross:

CrossTable(Action,Date) load * Inline [

id, Demande, Publication, Candidature,Creation

1, 15/01/2017, , 03/01/2017,10/12/2016

2, 12/03/2016, 15/01/2015, , 01/03/2014

3, 05/03/2017, 04/03/2017, 06/03/2017, 05/01/2015

];

NoConcatenate

t0:

load id, Action ,Date Resident cross  Where id='1' and  len(trim(Date))<>0 order by id,Date ;

drop Table cross;

final:

load  id Resident t0;

for i=0 to NoOfRows('t0')-1

let vAction$(i)= Peek('Action',$(i),'t0');

let vRow= Peek('id',$(i),'t0');

load Distinct id,  '$(vAction$(i))' as "Action$(i)"

Resident t0;

next i

drop Table t0;

This works perfectly if there was only ONE id..

But if there were more than one ID, the script will be executing and we'll have so many actions since the loop will go on to the last row..

The question is, how can this treatment be seperated by id?

I mean,I treat every id apart then concatenate the whole thing?

Thanks a lot !

[sunny_talwar · Accepted solution]

Try this

cross:

CrossTable(Action, Date)

LOAD * INLINE [

    id, Demande, Publication, Candidature, Creation

    1, 15/01/2017, , 03/01/2017, 10/12/2016

    2, 12/03/2016, 15/01/2015, , 01/03/2014

    3, 05/03/2017, 04/03/2017, 06/03/2017, 05/01/2015

];

Table:

NoConcatenate

LOAD id,

Action,

Date,

If(id = Previous(id), RangeSum(Peek('Num'), 1), 0) as Num

Resident cross

Where Len(Trim(Date)) > 0

Order By id, Date;

DROP Table cross;

FinalTable:

LOAD Distinct id

Resident Table;

FOR i = 1 to FieldValueCount('Num')

LET vNum = FieldValue('Num', $(i));

Left Join (FinalTable)

LOAD id,

Action as [action$(vNum)]

Resident Table

Where Num = '$(vNum)';

NEXT

DROP Table Table;

[OmarBenSalem]

You; my friend... you're The Genius !

I just love you

[OmarBenSalem]

One stupid question though, how can we begin with Action1 instead of Action0?

[sunny_talwar]

Change 0 to 1 here

cross:

CrossTable(Action, Date)

LOAD * INLINE [

    id, Demande, Publication, Candidature, Creation

    1, 15/01/2017, , 03/01/2017, 10/12/2016

    2, 12/03/2016, 15/01/2015, , 01/03/2014

    3, 05/03/2017, 04/03/2017, 06/03/2017, 05/01/2015

];

Table:

NoConcatenate

LOAD id,

Action,

Date,

If(id = Previous(id), RangeSum(Peek('Num'), 1), 0) as Num

Resident cross

Where Len(Trim(Date)) > 0

Order By id, Date;

DROP Table cross;

FinalTable:

LOAD Distinct id

Resident Table;

FOR i = 1 to FieldValueCount('Num')

LET vNum = FieldValue('Num', $(i));

Left Join (FinalTable)

LOAD id,

Action as [action$(vNum)]

Resident Table

Where Num = '$(vNum)';

NEXT

DROP Table Table;