Load field containg numbers as matrix with numbers as fieldnames

[#1]

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

[jagan]

Hi,

Check the attached file for solution.

Regards,

jagan.

[swuehl · Accepted solution]

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;

[#4]

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 😃

[swuehl]

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

[#6]

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