For Loop In Qlikview

[rahulsingh12]

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

[rahulsingh12]

Hi,

I have used the below code but does not seem to be giving the needed output.

A:

LOAD * INLINE [

version, Vendor, Region, Core, Amount

1, A, North, North-East, 100

2, A, South, South-, 200

3, A, South2, -West, 200

4, A, South2, South-West, 100

5, A, South, South-West, 150

6, A, South1, South1-West, 150

7, A, South1, South1-West, 100

8, A, South1, South1-West1, 100

1, B, North, North-East, 100

2, B, South, South-, 100

3, B, South, -West, 100

4, B, South2, South-West, 100

5, B, South, South-West, 200

6, B, South1, South1-West, 200

7, B, South1, South1-West, 100

8, B, South1, South1-West1, 100

];

New:

CrossTable(Field,Value,3)

LOAD Vendor,

    version,

    Amount, 

    Region, 

    Core

Resident A;

DROP Table A;

Final:

LOAD Vendor,

    version,

    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;

Am I missing something?

Regards,

Rahul

[Kushal_Chawda]

Code seems correct to me

what is the output you are getting with this code and what is expected?

[rahulsingh12]

Hi,

I got the result.

Thanks a lot.

I need to learn about using crosstable.

Anyways thanks!!