Consolidation of multiple columns in one

[Anonymous]

Hello all,

I implemented the following sample by for-loop how ever I did not get the proper result as desired:

data source:

City	January	February	March
New York	100	120	130
LA	200	220	230
Washington	300	320	330
Seatthle	400	420	430

My target is to reduce the number of columns to only three like

City     month     value

The first and easiest way is to concatenate columns as follows:

Target:

load

City,

'January' as month,

January as value

from xxx.csv;

concatenate (Target)

load

City,

'February' as month,

February as value

from xxx.csv;

concatenate (Target)

load

City,

'March' as month,

March as value

from xxx.csv;

but this is not the elegant way pf programing. So I tried to implement another way but without success:

tab1:

Load

null() as City,

null() as month,

null() as value

autogenerate 1;

for i =2 to NoOfFields(Target) step 1

let vFName = FieldName('$(i)','Target');

concatenate(tab1)

Load

City,

'['&'$(vFName)'&']' as month,

'$(vFName)' as value

Resident Target;

next

But something is wronge in this code and I do not know what exaclty? Any idea?

Thank you for help!

[petter · Accepted solution]

Guess what? QlikView has the prefect solution for you by providing the CrossTable prefix for the LOAD statement:

DATA:
CROSSTABLE(Month,Value) 
LOAD * INLINE [ 
City,January,February,March
New York,100,120,130
LA,200,220,230
Washington,300,320,330
Seattle,400,420,430
];

[Anonymous]

Thank you very much Petter.