topic Concatenate in a loop in QlikView

Concatenate in a loop

jdf — Wed, 23 Nov 2011 20:20:51 GMT

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;

Re: Concatenate in a loop

Wed, 23 Nov 2011 21:32:16 GMT

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.

Re: Concatenate in a loop

Miguel_Angel_Baeyens — Wed, 23 Nov 2011 21:54:53 GMT

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

Concatenate in a loop

jdf — Wed, 23 Nov 2011 22:02:30 GMT

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.

Re: Concatenate in a loop

Miguel_Angel_Baeyens — Wed, 23 Nov 2011 22:06:40 GMT

Hi Jacob,

Force concatenation using the CONCATENATE keyword before the first LOAD.

Hope that helps.

Re: Concatenate in a loop

Wed, 23 Nov 2011 22:16:10 GMT

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?

Re: Concatenate in a loop

jdf — Wed, 23 Nov 2011 22:21:43 GMT

I have tried that as well

Concatenate in a loop

rwunderlich — Thu, 24 Nov 2011 00:55:27 GMT

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

Concatenate in a loop

jdf — Mon, 28 Nov 2011 00:57:16 GMT

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;