Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

ni_avoss
Not applicable

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

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

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

12 Replies
dt_adamwilson
Not applicable

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

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

eg:

     Generic LOAD * FROM abc.csv;

ni_avoss
Not applicable

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

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
Not applicable

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

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
Not applicable

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

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

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

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

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

Did it work for you?

Gysbert_Wassenaar
Not applicable

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

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

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

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

ni_avoss
Not applicable

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

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