Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

flottmen
New Contributor

Create Table Loop Question

This seems like it should be simple but its not acting as expected.  I am trying to loop through what is essentially a generic table looking for certain name/value pairs.  I would like to create a table for each name in my list with the value and identifier from the source data. I am aware of the Generic Load but want to performance test this strategy against it.  Here is my code:

//This is my list of names from the source table.

UniqueAffiliateDataNames:

LOAD * INLINE [

  DataNames

  'IPAddress'

  'ListID',

  'SubID',

  'RepID',

  'CampaignId',

  'SubCampaignId',

  'UniqueId',

  'StudentId'

];

// date is simply to keep the table lean

LET vStartDate = DATE('2017-02-01', 'YYYY-MM-DD hh:mm:ss');

FOR EACH vDataName IN FieldValueList('DataNames')

  [$(vDataName)]:

  LOAD LeadId, Value

    FROM [$(vFilePath)LeadSharedData.qvd] (qvd)

    WHERE Name = '$(vDataName)'

    AND Category = 'F'

        AND DateEntered > '$(vStartDate)';

NEXT vDataName

DROP TABLE UniqueAffiliateDataNames;

When I run this script in the debugger I see the names populate in the Table name and in the Where clause as expected.  However, when the job finishes, I only seem to get one table (with the first value from the list).

I feel like I am missing something simple here. 

Tags (1)
1 Solution

Accepted Solutions
MVP
MVP

Re: Create Table Loop Question

May be you need NoConcatenate?

FOR EACH vDataName IN FieldValueList('DataNames')

  [$(vDataName)]:

  NoConcatenate

  LOAD LeadId, Value

    FROM [$(vFilePath)LeadSharedData.qvd] (qvd)

    WHERE Name = '$(vDataName)'

    AND Category = 'F'

        AND DateEntered > '$(vStartDate)';

NEXT vDataName

4 Replies
MVP
MVP

Re: Create Table Loop Question

May be you need NoConcatenate?

FOR EACH vDataName IN FieldValueList('DataNames')

  [$(vDataName)]:

  NoConcatenate

  LOAD LeadId, Value

    FROM [$(vFilePath)LeadSharedData.qvd] (qvd)

    WHERE Name = '$(vDataName)'

    AND Category = 'F'

        AND DateEntered > '$(vStartDate)';

NEXT vDataName

flottmen
New Contributor

Re: Create Table Loop Question

That seemed to do it.  Does Qlik simply ignore the Table name on the subsequent tables?  It seems like naming a table should tell Qlik that I want it to be separate.

Table1:

Load Name, Value

FROM Data

Table2:

Load Name, Value

FROM OtherData

Seems like this should be loaded as two tables.  Qlik automatically joins them because they have the same field names?

MVP
MVP

Re: Create Table Loop Question

Was just talking about this in another thread:

Difference between Synthetic Keys and Concatenation?

When the two tables have same number of fields and all the fields are named the exact same, QlikView will Auto-Concatenate them unless you tell it otherwise. By supplying NoConcatenate, you are telling QlikView that you want these to be in separate tables and not put them all in one table.

flottmen
New Contributor

Re: Create Table Loop Question

Ok.  Thank you for your help.