topic Re: Use field content as name in load in QlikView

Use field content as name in load

martynlloyd — Tue, 26 Aug 2014 10:32:27 GMT

Does anyone have a solution for variable field names, based on table content, in a Load statement?

There are lots of posts on this subject, but I can't find any solutions.

Example, this would be ideal if it worked...

LOAD
     DealerID,
     KPIYear,
     [KPI Description],
     Delivered as $([KPI Description])
FROM
$(vQVDPath)\KPDAllDeliveries.qvd (qvd)
Where KPIID='S1';

Regards,

Re: Use field content as name in load

MarcoWedel — Tue, 26 Aug 2014 10:38:33 GMT

use a generic load

Re: Use field content as name in load

MarcoWedel — Tue, 26 Aug 2014 10:39:11 GMT

please postr example data

thanks

regards

Marco

Re: Use field content as name in load

sundarakumar — Tue, 26 Aug 2014 10:40:40 GMT

Delivered retruns many rows in accordance with the no of rows in the table which cannot be stoed into a variable.

Try using a peek and get a value out of it to store it into a variable. that should work.

I assume we have the requirend table name in the field Delivered. Please let me know if am wrong or if u need more info.

-sundar

Re: Use field content as name in load

tresB — Tue, 26 Aug 2014 10:44:46 GMT

A sort of working script:

For Loop=1 to 10

Load
10+$(Loop) as $(Loop)
AutoGenerate 1;
Next

Update: Even you can do like:

For Loop=1 to 10
Let vField='Field'&$(Loop);
Load
10+$(Loop) as $(vField)
AutoGenerate 1;

Next

Re: Use field content as name in load

martynlloyd — Tue, 26 Aug 2014 11:01:58 GMT

...and then join them back together.

Is that the most efficient? I need to export the matrix as a txt file.

Regards,

Re: Re: Use field content as name in load

MarcoWedel — Tue, 26 Aug 2014 11:52:59 GMT

Hi,

you coud try:

tabDataTemp:
LOAD *,
     AutoNumberHash128(DealerID, KPIYear) as ID
INLINE [
    DealerID, KPIYear, KPI Description, Delivered
    1, 2014, KPI1, 100
    1, 2013, KPI1, 200
    1, 2012, KPI1, 300
    2, 2014, KPI1, 130
    2, 2013, KPI1, 150
    2, 2013, KPI2, 250
    3, 2014, KPI1, 520
    3, 2014, KPI2, 180
    3, 2013, KPI1, 380
    3, 2013, KPI2, 730
    4, 2014, KPI1, 260
    4, 2014, KPI2, 280
    4, 2014, KPI3, 930
    4, 2013, KPI1, 580
    4, 2013, KPI2, 360
    4, 2013, KPI3, 620
];

tabKPI:
Generic LOAD
  ID,
  [KPI Description],
  Delivered
Resident tabDataTemp;

tabData:
LOAD Distinct ID, DealerID, KPIYear Resident tabDataTemp;

DROP Table tabDataTemp;

FOR i = NoOfTables()-1 to 0 STEP -1
  LET vTab=TableName($(i));
  IF WildMatch('$(vTab)', 'tabKPI.*') THEN
  LEFT JOIN (tabData) LOAD * RESIDENT [$(vTab)];
  DROP TABLE [$(vTab)];
  ENDIF
NEXT i

resulting in only one table:

This script uses code from Rob Wunderlich to combine the seperate tables generated by the generic load into one:

Re: One column with all features (1 feature - 1 row)

There is also one fine post regarding the combination of tables created by a generic load by Henric Cronström:

http://community.qlik.com/blogs/qlikviewdesignblog/2014/03/31/generic

hope this helps

regards

Marco

Re: Re: Re: Use field content as name in load

MarcoWedel — Tue, 26 Aug 2014 12:02:18 GMT

on the other hand, you could just create a simple pivot table without any transformation in the script:

and then export to txt from there:

hope this helps

regards

Marco

Re: Use field content as name in load

martynlloyd — Thu, 28 Aug 2014 11:45:14 GMT

Actually, I have worked out that the best method is to create a pivot chart and then use a

OnPostReload action to export the object using obj.ExportBiff

Thanks to Daniel Viklinder

Regards,