Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
rahulsingh12
Contributor III
Contributor III

For Loop In Qlikview

hi,

I have  a table which has some 50 fields.

A:

Vendor     Version     Region     State     Amount

A               1               EAST          AP          140

A               2               WEST         KA          150

A               3               SOUTH       MP         144

AB              1               EAST          AP          140

AB              2               WEST         KA          150

AB              3               SOUTH       MP         144

I wanted to get an output which looks something like this

B:

VENDOR     VERSION     FIELD             OLD               NEW

A                    1                REGION          -                       EAST

A                    2               REGION          EAST               WEST

A                    3               REGION            WEST             SOUTH 

A                     1               STATE               -                         AP

AB                    1              REGION          -                         EAST            and so on..


The old Column is nothing but the previous value of the given Field and New Column is nothing but the current value of the Field.

I could get the desired result using concatenate.I created a table with the needed structure and started concatenating the values for each column A,but this way is only effective if the number of columns is less

If the number of columns in table A increases to 50 or 100 concatenating is not  an efficient way of doing it.

So I wanted to create a loop which takes the column name and its value to give the needed result.

Can someone help me with the looping?


Regards

Rahul

1 Solution

Accepted Solutions
Kushal_Chawda

try this

Data:

load * Inline [

Vendor,Version,    Region,    State,    Amount

A,              1,              EAST,          AP,          140

A ,              2,              WEST,        KA,          150

A,              3,              SOUTH,      MP,        144

AB,              1,              EAST,          AP,          140

AB,              2,              WEST ,        KA,          150

AB,              3,              SOUTH,      MP,        144 ];

New:

CrossTable(Field,Value,3)

LOAD Vendor,

    Version,

    Amount,  

    Region,  

    State

Resident Data;

DROP Table Data;

Final:

LOAD Vendor,

    Version,

    Amount,

    Field,  

    Value as NEW,

    if(Vendor=Previous(Vendor) and Field=Previous(Field),Previous(Value)) as OLD

Resident New

Order by Vendor,Field,Version;

DROP Table New;

Capture.JPG

View solution in original post

12 Replies
arvind_patil
Partner - Specialist III
Partner - Specialist III

Hi Rahul,

You can create loop   like Below:

The function you have for converting the string '01/01/2012' into a date is incorrect - the function Date is used for formatting a date field, whilst the function you require, Date#, converts a string to a number.

You could change the line to read:

FOR i=Date#('01/01/2012','DD/MM/YYYY') to $(y)

The suffix in the QVD file name if this worked, would be the integer number for the day (ie. today is 41372), and you probably want it in a YYYYMMDD format.

Try this syntax instead:

let vDays = num#(today() - date#('01/01/2012','DD/MM/YYYY'));

FOR i = 0 to vDays

  let vSuffix = Date(today() - vDays + i, 'YYYYMMDD');

  TRACE $(i) - $(vSuffix);

NEXT i

You can use $(vSuffix) in the same way as you do $(i) in your code above.

for More Help

For..next ‒ QlikView

THanks,

Arvind Patil

vinieme12
Champion III
Champion III

Use crosstable load

The Crosstable Load

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
vinieme12
Champion III
Champion III

Where did you copy paste this from?

It's irrelevant to the query !

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
Anil_Babu_Samineni

May be create some thing like below

Fact:

LOAD Vendor, Version, Region, State, Amount from Fact;

Left Join (Fact)

Load *, <Logic for FIELD> as FIELD;

Left Join (Fact)

Load *, <Logic for OLD> as OLD;

Left Join (Fact)

Load *, <Logic for NEW> as NEW;

// Do here the Generic Key word

Final:

NoConcatenate

Generic Load * Resident Fact;

Drop Table Fact;

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
rahulsingh12
Contributor III
Contributor III
Author

Hi,

this solution is useful if the number of fields is less but if you have 100 or 200 fields to do the join from then it will be a tedious task to write so many joins.

That is why i was thinking of some loop to loop through fields.

Regards,

Anil_Babu_Samineni

+ its.anandrjs‌ may helps you

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
vinieme12
Champion III
Champion III

post a sample that best represents your scenario

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
its_anandrjs

Read some threads on the missing data might be helpful for you.

Regards

Anand

Kushal_Chawda

try this

Data:

load * Inline [

Vendor,Version,    Region,    State,    Amount

A,              1,              EAST,          AP,          140

A ,              2,              WEST,        KA,          150

A,              3,              SOUTH,      MP,        144

AB,              1,              EAST,          AP,          140

AB,              2,              WEST ,        KA,          150

AB,              3,              SOUTH,      MP,        144 ];

New:

CrossTable(Field,Value,3)

LOAD Vendor,

    Version,

    Amount,  

    Region,  

    State

Resident Data;

DROP Table Data;

Final:

LOAD Vendor,

    Version,

    Amount,

    Field,  

    Value as NEW,

    if(Vendor=Previous(Vendor) and Field=Previous(Field),Previous(Value)) as OLD

Resident New

Order by Vendor,Field,Version;

DROP Table New;

Capture.JPG