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: 
ni_avoss
Creator
Creator

How to transform values in one column to distinct columns (Data from a .csv)

Hi to all,

i am quite new to QV and was reading for a long time here which helped me a lot already.
But now i got to a point where i am stuck and couldn't find any solution yet.

The situation:

I am importing a .csv which got a column which distinct values i want to use as columns, while keeping the other columns.

Maybe this sample is a bit easier to understand:

original excerpt:

user_idkeyvalue
1cat1val1
1cat2val2
1cat3val3
2cat1val4
2cat2val5
2cat3val6

it want this to be transformed to this:

user_idcat1cat2cat3
1val1val2val3
2val4val5val6

there are other columns (like ids, date) in the original i want to keep as well.


My first thoughts were running throught each line of the original with the help of a switch case/ ifs and checking for the key. but it seems i am running into the problem of concatenating it to the right id/row.

If think it would be great to have something like 'for each $variable in user_id (column)'. but this doesnt work either.

Any ideas? Do I make an obvious mistake?

Best

Alex

1 Solution

Accepted Solutions
Not applicable

Made some modifications to allow filednames with spaces and bad fieldnames so script won't fail.

(Basically enclosing the filedname with [])

Also added so you can manipulate the fieldname if you don't want spaces et.c. in it.

Cheers

// Original table. In this example substituted with an inline load.
Evil:
LOAD * INLINE
[
user_id, key, value
   1, cat1, val1
   1, cat2, val2
   1, cat3, val3
   2, cat1, val4
   2, cat2, val5
   2, cat3, val6
   3, cat4, val7  
   1, cat1, val8
   1, cat 5, val 9
   4, cat 6, val10
   4, cat_6, val11
   5, =1+1, val12 
];

// Get distinct values for fieldnames
DistinctFields:
LOAD DISTINCT key AS Field RESIDENT Evil;

// Add rownumber to get number of rows
FieldsWithRownumber:
LOAD Field, ROWNO() AS Row RESIDENT DistinctFields;

// Get number of rows
LET intMaxRow = PEEK('Row', -1, 'FieldsWithRownumber');

// Get first fieldvalue (0=first, 1=second...)
LET strFieldValue = PEEK('Field', 0, 'FieldsWithRownumber');

// Make manipulated fieldname (if there is bad characters or blanks that you don't want in the fieldname)
LET strFieldName = '[' & REPLACE(strFieldValue, ' ', '_') & ']';

// Create a new table with the first field
ResultTable:
LOAD
   user_id,
   value AS $(strFieldName)
RESIDENT
   Evil
WHERE
   key = '$(strFieldValue)'
;

// Loop rest of the fields and join to the result table
FOR intRowNo = 1 to $(intMaxRow) - 1
   // Get next field
   LET strFieldValue = PEEK('Field', intRowNo, 'FieldsWithRownumber');
   // Make manipulated fieldname
   LET strFieldName = '[' & REPLACE(strFieldValue, ' ', '_') & ']';
   // Join this field to result table
   JOIN (ResultTable)
   LOAD
      user_id,
   value AS $(strFieldName)
   RESIDENT
      Evil
   WHERE
      key = '$(strFieldValue)';
NEXT intRowNo

// Drop all tables except result table
DROP TABLES
   Evil,
   DistinctFields,
   FieldsWithRownumber
;  

// Missing values for a user-key combination vill result in a null value for that field.
// If multiple values exists for the same user-key combination,  multiple rows will be created for this combination.
// When you manipulate fieldnames, 2 or more fields may ending up being named the same. example: cat 6 and cat_6

View solution in original post

12 Replies
adamwilson
Partner - Creator
Partner - Creator

you could use a generic load which may give the desired outcome

eg:

     Generic LOAD * FROM abc.csv;

ni_avoss
Creator
Creator
Author

thanks for the hint.

this results in qlikview generating a table for each user_id (which are a few thousand) and also, the columns are mainly the same, so no change

or am i using it wrong?

(loading with (txt, codepage is 1252, embedded labels, delimiter is ';', msq); )

nagaiank
Specialist III
Specialist III

Please see the attached solution, which loads the data as in your first table. The pivot table chart generated from the data displays your second table.

ni_avoss
Creator
Creator
Author

hi thanks for that.
I may should have mentioned it, but its not about design / gui.

showing the data nicely in charts is not a problem at all...

it need this for the data model for selection purposes

Not applicable

Made some modifications to allow filednames with spaces and bad fieldnames so script won't fail.

(Basically enclosing the filedname with [])

Also added so you can manipulate the fieldname if you don't want spaces et.c. in it.

Cheers

// Original table. In this example substituted with an inline load.
Evil:
LOAD * INLINE
[
user_id, key, value
   1, cat1, val1
   1, cat2, val2
   1, cat3, val3
   2, cat1, val4
   2, cat2, val5
   2, cat3, val6
   3, cat4, val7  
   1, cat1, val8
   1, cat 5, val 9
   4, cat 6, val10
   4, cat_6, val11
   5, =1+1, val12 
];

// Get distinct values for fieldnames
DistinctFields:
LOAD DISTINCT key AS Field RESIDENT Evil;

// Add rownumber to get number of rows
FieldsWithRownumber:
LOAD Field, ROWNO() AS Row RESIDENT DistinctFields;

// Get number of rows
LET intMaxRow = PEEK('Row', -1, 'FieldsWithRownumber');

// Get first fieldvalue (0=first, 1=second...)
LET strFieldValue = PEEK('Field', 0, 'FieldsWithRownumber');

// Make manipulated fieldname (if there is bad characters or blanks that you don't want in the fieldname)
LET strFieldName = '[' & REPLACE(strFieldValue, ' ', '_') & ']';

// Create a new table with the first field
ResultTable:
LOAD
   user_id,
   value AS $(strFieldName)
RESIDENT
   Evil
WHERE
   key = '$(strFieldValue)'
;

// Loop rest of the fields and join to the result table
FOR intRowNo = 1 to $(intMaxRow) - 1
   // Get next field
   LET strFieldValue = PEEK('Field', intRowNo, 'FieldsWithRownumber');
   // Make manipulated fieldname
   LET strFieldName = '[' & REPLACE(strFieldValue, ' ', '_') & ']';
   // Join this field to result table
   JOIN (ResultTable)
   LOAD
      user_id,
   value AS $(strFieldName)
   RESIDENT
      Evil
   WHERE
      key = '$(strFieldValue)';
NEXT intRowNo

// Drop all tables except result table
DROP TABLES
   Evil,
   DistinctFields,
   FieldsWithRownumber
;  

// Missing values for a user-key combination vill result in a null value for that field.
// If multiple values exists for the same user-key combination,  multiple rows will be created for this combination.
// When you manipulate fieldnames, 2 or more fields may ending up being named the same. example: cat 6 and cat_6

Not applicable

Did it work for you?

Gysbert_Wassenaar

If you only have three categories you can do it like this:

T1:

LOAD user_id, key, value

from ...somewhere...;

T2:

load user_id, value as cat1

Resident T1

where key = 'cat1';

join load user_id, value as cat2

Resident T1

where key = 'cat2';

join load user_id, value as cat3

Resident T1

where key = 'cat3';

Drop Table T1;


talk is cheap, supply exceeds demand
Not applicable

That's exactly what my code does but only not hardcoded to 3 specific values...

ni_avoss
Creator
Creator
Author

hey,

thanks you very much! was the right approach.

for the protocol:

i intended to use some 15 columns and qlikview wasnt able to handle this - this script caused a crash every time.
i reduced it now to 6 columns which works fine

cheets