topic Re: Loop or other suggestions? in QlikView

Loop or other suggestions?

Anonymous — Mon, 08 Sep 2014 18:16:22 GMT

Hi all.

Can you help me solve this one?

In the attached file I have the sheet "Base", where I have sorted the fields "NAME" and "GROUP" based on the field "PRIORITY_ALL".

Now I want to loop the table into a new table where all the names are given a new priority called "PRIORITY_GROUP" based on their priority in "PRIORITY_ALL" and which group in "GROUP" they belong to.

I have manually illustrated the result it's supposed to be in the sheet "After group priority".

Finally it should be used for creating a pivot table as seen in the sheet "Pivot solution" if it makes more sense to you.

I really hope you can help me with this Little issue.

Best Regards,

Johnni Kirstein

Re: Loop or other suggestions?

vgutkovsky — Mon, 08 Sep 2014 18:22:49 GMT

That's easy enough. Assuming your first table is named "data" then you can do something like this:

LOAD

if(GROUP = previous(GROUP),

peek(PRIORITY_GROUP)+1,

) as PRIORITY_GROUP

RESIDENT data

ORDER BY GROUP,PRIORITY_ALL

;

Regards,

Vlad

Re: Loop or other suggestions?

Gysbert_Wassenaar — Mon, 08 Sep 2014 18:28:30 GMT

See attached qvw

Re: Loop or other suggestions?

MarcoWedel — Mon, 08 Sep 2014 19:51:56 GMT

Hi,

one possible solution for your sample data:

LOAD *,
    AutoNumber(PRIORITY_ALL, GROUP) as PRIORITY_GROUP
FROM [Sandbox_Loop.xlsx] (ooxml, embedded labels, table is Base);

hope this helps

regards

Marco

Re: Re: Loop or other suggestions?

MarcoWedel — Mon, 08 Sep 2014 20:00:25 GMT

Hi again,

for unsorted input tables you could extend this solution to:

tab1:
LOAD * FROM [Sandbox_Loop.xlsx] (ooxml, embedded labels, table is Base);

Left Join
LOAD NAME, AutoNumber(PRIORITY_ALL, GROUP) as PRIORITY_GROUP
Resident tab1
Order By PRIORITY_ALL;

hope this helps

regards

Marco

Re: Loop or other suggestions?

Anonymous — Tue, 09 Sep 2014 05:34:47 GMT

All the answers are correct but Marco had the easiest one.

What I had in mind to solve it was making several loads of the table where each load contains a unique value from the field "GROUP". I think this is still possible but not the best way to do it.

Thank you all for your help.

Best Regards,

Johnni Kirstein