Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a matrix of Time and client-IDs.
Load * inline [Time, ID
01,1
02,12
03,245
03,123
04,245
06,174
06,123
07,12];
And would like to load this sparse matrix:
Time 1 12 123 174 245
01 1 0 0 0 0
02 0 1 0 0 0
03 0 0 0 1 1
04 0 0 0 0 1
06 0 0 1 1 0
07 0 1 0 0 0
How can I do this? Any ideas?
Best,
Jonas
If you want to create the crosstable in the script, you could use a GENERIC Load.
This will create several tables, to join them back into one you could use something like suggested here:
http://community.qlik.com/message/78337
to get something like:
INPUT:
Load * inline [
Time, ID
01,1
02,12
03,245
03,123
04,245
06,174
06,123
07,12];
tmp:
Generic LOAD *, 1 as Value Resident INPUT;
Result:
load distinct Time resident INPUT;
drop table INPUT;
FOR i = 0 to NoOfTables()
TableList:
LOAD TableName($(i)) as Tablename AUTOGENERATE 1
WHERE WildMatch(TableName($(i)), 'tmp.*');
NEXT i
FOR i = 1 to FieldValueCount('Tablename')
LET vTable = FieldValue('Tablename', $(i));
LEFT JOIN (Result) LOAD * RESIDENT $(vTable);
DROP TABLE $(vTable);
NEXT i
drop table TableList;
Hi,
Check the attached file for solution.
Regards,
jagan.
If you want to create the crosstable in the script, you could use a GENERIC Load.
This will create several tables, to join them back into one you could use something like suggested here:
http://community.qlik.com/message/78337
to get something like:
INPUT:
Load * inline [
Time, ID
01,1
02,12
03,245
03,123
04,245
06,174
06,123
07,12];
tmp:
Generic LOAD *, 1 as Value Resident INPUT;
Result:
load distinct Time resident INPUT;
drop table INPUT;
FOR i = 0 to NoOfTables()
TableList:
LOAD TableName($(i)) as Tablename AUTOGENERATE 1
WHERE WildMatch(TableName($(i)), 'tmp.*');
NEXT i
FOR i = 1 to FieldValueCount('Tablename')
LET vTable = FieldValue('Tablename', $(i));
LEFT JOIN (Result) LOAD * RESIDENT $(vTable);
DROP TABLE $(vTable);
NEXT i
drop table TableList;
Wow, Stefan!
You always have some very fancy solutions. I would not have found this one otherwise. Cheers, that's awesome!
I wonder though, how this performs if there are other preloaded tables, since there is "NoOfTables" in the script.
Maybe I should load this table in a separate script and then just import the .qvd file, or do you see other solutions?
Best,
Jonas 😃
I don't think that's necessary.
Note that the Generic load will create a lot of tables, but all will have a table name like 'tmp.245', 'tmp.123' etc., i.e. the Tablename we used for the Generic load (here: tmp) and then the field values for your ID.
Please note further, that there is a where clause in your TableList load:
WHERE WildMatch(TableName($(i)), 'tmp.*');
here we match table names that have a preceding 'tmp.', i.e. that were created in our Generic Load.
So preloading other tables should not be a problem, if their names don't start with 'tmp.'
Regards,
Stefan
Aha, awesome!
But this taes like years to finish. I am loading a file with per minute data. it takes like 3 seconds for every hour to load -> 7.3 hours for a year to load! This is huge! Is there no other way for loading it?
Best,
Jonas