Concatenate in a loop

[jdf]

Good Afternoon,

I am having a problem creating a single table in a loop.  I have the code below that counts the number of files and combines 3 files in a loop.  Everything is working correctly except that the loop produces a table for each loop process and creates a synthetic key linking the tables together.  I would like them to concatenate together, and am confused why they aren't automatically concatenating as they all have the exact same field names.  Any help would be appreciated.

Thanks,

Jacob

Set NoOfFiles = 0;
For each file in filelist ('D:\ar\Production Scripts 11-4-11\Data\MetaData\System*.qvw.Fields.tab');
Let NoOfFiles = NoOfFiles + 1;
next

for I = 1 to $(NoOfFiles)
load
FieldName,
DistinctOnly,
Cardinal,
TotalCount,
PossibleCount,
HasInfo,
IsLocked,
AlwaysOneSelected,
AndMode
FROM (txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

LEFT JOIN
LOAD FieldName,
TableName
FROM (txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

LEFT JOIN
LOAD TableName,
Loose,
#Records,
#Fields,
#Keys
FROM (txt, codepage is 1252, embedded labels, delimiter is '\t', msq);
next I;

[#2]

I think that the problem is that in the second loop the first load sentence try to concatenate with the table loaded in the first loop, before have been done the LEFT JOINS and in consecuence without the fields added in this JOINS.

Maybe you can do it if you try concatenate the 3 tables that you are making LEFT JOIN in 3 separate tables in the loop and after that make the joins between this 3 concatenated tables.

[Miguel_Angel_Baeyens]

Hi Jacob,

Have you tried setting a label to the table instead leaving that QlikView sets a random name?

for I = 1 to $(NoOfFiles)
Table: // fixed for all the files
load
FieldName, 
...

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[jdf]

Claudio - Thanks for the suggestion but that won't work and the left joins need to be done in step.  If I join the tables right away they join on a 1 to many relationship which works nicely.  If I concatenate then left join I will be joining many to many which will cause duplicated data.

Miguel - Thanks for the suggestion but it didn't work I get the same result.

[Miguel_Angel_Baeyens]

Hi Jacob,

Force concatenation using the CONCATENATE keyword before the first LOAD.

Hope that helps.

[#6]

And if you store in a separate qvd and drop from the memory the table generated in each loop and then make a second loop to load from the qvd the tables again?

[jdf]

I have tried that as well

[rwunderlich]

I think if you want to join after every load, then you must continue to create three seperate tables and concatenate them together in a final step.

If you force CONCATENATE the tables while loading, you'll get the "spoiled table" problem. For example, after the first join, the target table will contain the new field "TableName". When you add rows in the second load, null vallue will be assigned for TableName in those new rows. The next join will match zero rows, as there will be no matches between the null TableName in the target table and the non-null TableName in the source table.

-Rob

http://robwunderlich.com

[jdf · Accepted solution]

Thanks for everyone's input I have added a step at the end renaming the fields and then dropping the original table, it works like a charm!

Set NoOfFiles = 0;
For each file in filelist ('D:\ar\Production Scripts 11-4-11\Data\MetaData\System*.qvw.Fields.tab');
Let NoOfFiles = NoOfFiles + 1;

next
for I = 1 to $(NoOfFiles)
Table:
load
FieldName,
DistinctOnly,
Cardinal,
TotalCount,
PossibleCount,
HasInfo,
IsLocked,
AlwaysOneSelected,
AndMode
FROM (txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

LEFT JOIN
LOAD
FieldName,
TableName
FROM (txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

LEFT JOIN
LOAD
TableName,
Loose,
#Records,
#Fields,
#Keys
FROM (txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

tmpMetadata:
Load 
FieldName    as [Field Name],
// DistinctOnly  as [Count Distinct],
Cardinal   as [Count Distinct],
TotalCount   as [Count Total],
// PossibleCount  as [Count Possible],
// HasInfo    as [Has Info],
// IsLocked   as [Is Locked],
// AlwaysOneSelected  as [Always One Selected],
// AndMode    as [And Mode],
TableName   as [Table Name],
// Loose    as [Loose],
#Records   as [# of Records],
#Fields    as [# of Fields],
#Keys    as [# of Keys],
$(I)    as [File Num]
resident Table;

drop Table Table;
next I;