Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

Load/Append recursively in script

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:  

   

NameAgeAddress
John36New York
Tom42Detroit
Henry45Washington

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:

   

NameChildNamesChildAgeChildGender
JohnHarry3M
JohnShawn5M
TomPaul12M
TomJerry10M
TomSamantha7F
HenryClaire11F
 
                
Tags (1)
1 Solution

Accepted Solutions
Partner
Partner

Re: Load/Append recursively in script

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

View solution in original post

6 Replies

Re: Load/Append recursively in script

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

Partner
Partner

Re: Load/Append recursively in script

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

View solution in original post

Not applicable

Re: Load/Append recursively in script

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

Partner
Partner

Re: Load/Append recursively in script

You would wrap the variable in single quotes:

LOAD '$(vNameEntry)' as Name,

...

...

...

Not applicable

Re: Load/Append recursively in script

Thanks Peter.

Not applicable

Re: Load/Append recursively in script

Thanks Oscar.