4 Replies Latest reply: Aug 2, 2018 7:08 AM by omar bensalem

# Help required

Hi guys ( stalwar1  petter-s jontydkpi )

Suppose I have this:

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:

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:

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 !

• ###### Re: Help required

Try this

cross:

CrossTable(Action, Date)

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

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:

Resident Table;

FOR i = 1 to FieldValueCount('Num')

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

Left Join (FinalTable)

Action as [action\$(vNum)]

Resident Table

Where Num = '\$(vNum)';

NEXT

DROP Table Table;

• ###### Re: Help required

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

I just love you

• ###### Re: Help required

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

• ###### Re: Help required

Change 0 to 1 here

cross:

CrossTable(Action, Date)

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

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:

Resident Table;

FOR i = 1 to FieldValueCount('Num')

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

Left Join (FinalTable)