topic Re: Load/Append recursively in script in QlikView

Load/Append recursively in script

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Load/Append recursively in script

Peter_Cammaert — Thu, 26 May 2016 15:54:16 GMT

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

Re: Load/Append recursively in script

oscar_ortiz — Thu, 26 May 2016 16:25:16 GMT

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);

Re: Load/Append recursively in script

Thu, 26 May 2016 16:52:00 GMT

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

Re: Load/Append recursively in script

oscar_ortiz — Thu, 26 May 2016 16:59:15 GMT

You would wrap the variable in single quotes:

LOAD '$(vNameEntry)' as Name,

...

Re: Load/Append recursively in script

Thu, 26 May 2016 17:01:07 GMT

Thanks Peter.

Re: Load/Append recursively in script

Thu, 26 May 2016 17:03:57 GMT

Thanks Oscar.