Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Partner
Partner

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

Re: Re: Use field content as name in load

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

8 Replies

Re: Use field content as name in load

use a generic load

Re: Use field content as name in load

please postr example data

thanks

regards

Marco

sundarakumar
Valued Contributor II

Re: Use field content as name in load

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

MVP
MVP

Re: Use field content as name in load

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 

Partner
Partner

Re: Use field content as name in load

...and then join them back together.

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

Regards,

M.

Re: Re: Use field content as name in load

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

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

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

Highlighted
Partner
Partner

Re: Use field content as name in load

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.