Load/Append recursively in script

[#1]

Hi

I am new to Qlikview and I am trying to achieve something as follows while trying to load data. I appreciate if experts can help with the syntax to do so.

Many Thanks

Rahul

TAB1:

    LOAD Name,

               Age,

               Address

    FROM (txt, codepage is 1252, embedded labels, delimiter is ',', msq);

TAB2:

    FOR EACH <NameEntry> IN TAB1.Name

    LOAD  Name,

                ChildNames,

                ChildAge,

                ChildGender

    FROM (txt, codepage is 1252, embedded labels, delimiter is ',', msq);

So if TAB1 is as below:  

Name	Age	Address
John	36	New York
Tom	42	Detroit
Henry	45	Washington

and John 's family data is in JohnFamily.csv, Tom's family data is in TomFamily.csv, Henry's family data is in HenryFamily.csv. The TAB2 dataset should look as below:

Name	ChildNames	ChildAge	ChildGender
John	Harry	3	M
John	Shawn	5	M
Tom	Paul	12	M
Tom	Jerry	10	M
Tom	Samantha	7	F
Henry	Claire	11	F

 

[Peter_Cammaert]

Before resorting to VBScript FOR loops (often much slower than native QlikView statements), try this instead of the current TAB2 code:

TAB2:

LEFT KEEP (TAB1)

LOAD Name,

     ChildNames,

     ChildAge,

     ChildGender

FROM (txt, codepage is 1252, embedded labels, delimiter is ',', msq);

If you run into trouble (the LEFT KEEP may not like the wildcard), you can first load all files into a single RESIDENT table using the wildcard character, and then LEFT KEEP that table to TAB1. The associations will be maintainbed and no tables will be JOINed.

Best,

Peter

[oscar_ortiz · Accepted solution]

You may try something like this:

TAB1:

LOAD Name,

     Age,

     Address

FROM (txt, codepage is 1252, embedded labels, delimiter is ',', msq);

FOR i = 0 to NoOfRows('TAB1') - 1

  LET vNameEntry = Peek('Name', i, 'TAB1');

  TAB2:

    LOAD  Name,

          ChildNames,

          ChildAge,

          ChildGender

    FROM (txt, codepage is 1252, embedded labels, delimiter is ',', msq);

NEXT

[#4]

Thanks Oscar. This works like a charm. I have a quick question. If *Family.csv does not have Name column. Then how is it possible to add Name column using vNameEntry.

LOAD $(vNameEntry) as Name

This does not work as it tries to LOAD Henry as Name, i.e., tries to find Henry column in TAB2.

Thanks

Rahul

[oscar_ortiz]

You would wrap the variable in single quotes:

LOAD '$(vNameEntry)' as Name,

...

...

...

[#6]

Thanks Peter.

[#7]

Thanks Oscar.