topic Create Table Loop Question in App Development

Create Table Loop Question

flottmen — Thu, 16 Feb 2017 19:05:55 GMT

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.

Re: Create Table Loop Question

sunny_talwar — Thu, 16 Feb 2017 19:41:01 GMT

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

Re: Create Table Loop Question

flottmen — Thu, 16 Feb 2017 19:51:26 GMT

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?

Re: Create Table Loop Question

sunny_talwar — Thu, 16 Feb 2017 19:54:12 GMT

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.

Re: Create Table Loop Question

flottmen — Thu, 16 Feb 2017 19:55:39 GMT

Ok. Thank you for your help.