Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

OmarBenSalem
Esteemed Contributor

Help required

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..

Capture.PNG

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..

Capture.PNG

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 !

1 Solution

Accepted Solutions

Re: Help required

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;

4 Replies

Re: Help required

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
Esteemed Contributor

Re: Help required

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

I just love you

OmarBenSalem
Esteemed Contributor

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)

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;

Community Browser