Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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_id | key | value |
---|---|---|
1 | cat1 | val1 |
1 | cat2 | val2 |
1 | cat3 | val3 |
2 | cat1 | val4 |
2 | cat2 | val5 |
2 | cat3 | val6 |
it want this to be transformed to this:
user_id | cat1 | cat2 | cat3 |
---|---|---|---|
1 | val1 | val2 | val3 |
2 | val4 | val5 | val6 |
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
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
you could use a generic load which may give the desired outcome
eg:
Generic LOAD * FROM abc.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); )
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.
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
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
Did it work for you?
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;
That's exactly what my code does but only not hardcoded to 3 specific values...
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