Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Load field containg numbers as matrix with numbers as fieldnames

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

1 Solution

Accepted Solutions
MVP
MVP

Load field containg numbers as matrix with numbers as fieldnames

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;

5 Replies
MVP
MVP

Re: Load field containg numbers as matrix with numbers as fieldnames

Hi,

Check the attached file for solution.

Regards,

jagan.

MVP
MVP

Load field containg numbers as matrix with numbers as fieldnames

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;

Not applicable

Re: Load field containg numbers as matrix with numbers as fieldnames

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 =)

MVP
MVP

Load field containg numbers as matrix with numbers as fieldnames

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

Not applicable

Re: Load field containg numbers as matrix with numbers as fieldnames

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

Community Browser