Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
martynlloyd
Partner - Creator III
Partner - Creator III

Use field content as name in load

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.

1 Solution

Accepted Solutions
MarcoWedel

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:

QlikCommunity_Thread_130769_Pic2.JPG.jpg

QlikCommunity_Thread_130769_Pic1.JPG.jpg

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

View solution in original post

8 Replies
MarcoWedel

use a generic load

MarcoWedel

please postr example data

thanks

regards

Marco

sundarakumar
Specialist II
Specialist II

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
MVP
MVP

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
Partner - Creator III
Partner - Creator III
Author

...and then join them back together.

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

Regards,

M.

MarcoWedel

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:

QlikCommunity_Thread_130769_Pic2.JPG.jpg

QlikCommunity_Thread_130769_Pic1.JPG.jpg

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:

QlikCommunity_Thread_130769_Pic3.JPG.jpg

and then export to txt from there:

QlikCommunity_Thread_130769_Pic5.JPG.jpg

QlikCommunity_Thread_130769_Pic4.JPG.jpg

hope this helps

regards

Marco

martynlloyd
Partner - Creator III
Partner - Creator III
Author

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.