topic Re: Join Table in QlikView

Join Table

tamilarasu — Wed, 04 Mar 2015 08:26:49 GMT

Hi,

I am new to qlikview.I want to add a specific cell value as column for all data in that file. I have found a solution but when i put the same code in loop i got wrong result.I am not sure where i am wrong.

My output should be like below screenshot

but i got the below output.

Script i tried

sub ScanFolder(Root)

for each FileExtension in 'xlsx'

for each FoundFile in filelist( Root & '\*.' & FileExtension)

FileData:

LOAD Sno,

Cust,

Prod,

[Sold Qty],

Price

FROM

[$(FoundFile)] (ooxml, embedded labels, table is Sheet1, filters(

Remove(Row, RowCnd(Interval, Pos(Top, 1), Pos(Top, 1), Select(1, 0)))

));

LOAD

B as Country

FROM

[$(FoundFile)] (ooxml, explicit labels, table is Sheet1)

WHERE RecNo() =1;

next FoundFile

next FileExtension

end sub

Call ScanFolder('C:\Users\Tamilarasu.Nagaraj\Desktop\New folder (2)') ;

Attached sample files. Can anyone help me.

Thank you in advance.

Kind Regards,

Tamil

Re: Join Table

Anonymous — Wed, 04 Mar 2015 08:40:49 GMT

Tamil,

Read Country in a variable and load it with the rest of data:

sub ScanFolder(Root)

for each FileExtension in 'xlsx'

for each FoundFile in filelist( Root & '\*.' & FileExtension)

cTable:

LOAD

B as Country

FROM

[$(FoundFile)] (ooxml, explicit labels, table is Sheet1)

WHERE RecNo() =1;

LET vCountry = Peek('Country', -1, 'cTable');

DROP Table cTable;

FileData:

LOAD $(vCountry) as Country,

Sno,

Cust,

Prod,

[Sold Qty],

Price

FROM

[$(FoundFile)] (ooxml, embedded labels, table is Sheet1, filters(

Remove(Row, RowCnd(Interval, Pos(Top, 1), Pos(Top, 1), Select(1, 0)))

));

next FoundFile

next FileExtension

end sub

Call ScanFolder('C:\Users\Tamilarasu.Nagaraj\Desktop\New folder (2)') ;

Re: Join Table

hic — Wed, 04 Mar 2015 08:44:09 GMT

You need a link between Country and the rest of the data. E.g.

for each FileExtension in 'xlsx'

for each FoundFile in filelist( '*.' & FileExtension)

Country:

LOAD

B as Country

FROM [$(FoundFile)] (ooxml, explicit labels, table is Sheet1)

WHERE RecNo() =1;

Let vCountry = Peek('Country',-1,'Country');

FileData:

LOAD

'$(FoundFile)' as FileName,

'$(vCountry)' as Country,

Sno,

Cust,

Prod,

[Sold Qty],

Price

FROM [$(FoundFile)] (ooxml, embedded labels, header is 1 lines, table is Sheet1);

next FoundFile

next FileExtension

HIC

Re: Join Table

simenkg — Wed, 04 Mar 2015 09:04:41 GMT

sub ScanFolder(Root)

FinalTable:

load 'Dummy' as Dummy autogenerate 1;

for each FileExtension in 'xlsx'

for each FoundFile in filelist( Root & '\*.' & FileExtension)

FileData:

LOAD Sno,

Cust,

Prod,

[Sold Qty],

Price

FROM

[$(FoundFile)] (ooxml, embedded labels, table is Sheet1, filters(

Remove(Row, RowCnd(Interval, Pos(Top, 1), Pos(Top, 1), Select(1, 0)))

));

outer join (FileData)

LOAD

B as Country

FROM

[$(FoundFile)] (ooxml, explicit labels, table is Sheet1)

WHERE RecNo() =1;

concatenate(FinalTable)

Load * resident FileData;

Drop table FileData;

next FoundFile

next FileExtension

Drop Field Dummy;

end sub

Call ScanFolder('C:\Users\Tamilarasu.Nagaraj\Desktop\New folder (2)') ;

Re: Join Table

tamilarasu — Wed, 04 Mar 2015 09:29:21 GMT

Hi Marc.

I got the below error while executing the above code. when I click ok button, it again shows error message for another country.

Re: Join Table

tamilarasu — Wed, 04 Mar 2015 09:31:34 GMT

Henric: Your solution is working fine.

Re: Join Table

tamilarasu — Wed, 04 Mar 2015 09:35:07 GMT

Simen: Your solution also working fine but I got an error message. Could you please tell me why we are using Dummy field here. Thank you

Re: Join Table

simenkg — Wed, 04 Mar 2015 09:44:41 GMT

The Dummy field is used to initialize the table. You cant create an empty table, as far as I know.

Re: Join Table

simenkg — Wed, 04 Mar 2015 09:45:18 GMT

Move the Drop Field Dummy; Statement after the Next statements and it should work (I have fixed it in my original answer).

Re: Join Table

Anonymous — Wed, 04 Mar 2015 09:46:56 GMT

Sorry Tamil, I missed commas

...

LOAD '$(vCountry)' as Country,

...

Marc.

Re: Join Table

tamilarasu — Wed, 04 Mar 2015 10:08:51 GMT

Nice and thank you for the explanation. Simon.

Re: Join Table

tamilarasu — Wed, 04 Mar 2015 10:13:23 GMT

Marc: It's fine now. and have a nice day.