Use field content as name in load

[martynlloyd]

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,

M.

[MarcoWedel]

use a generic load

[MarcoWedel]

please postr example data

thanks

regards

Marco

[sundarakumar]

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

[tresesco]

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 

[martynlloyd]

...and then join them back together.

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

Regards,

M.

[MarcoWedel · Accepted solution]

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

[MarcoWedel]

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

[martynlloyd]

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,

M.