Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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)') ;
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
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)') ;
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.
Henric: Your solution is working fine.
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
The Dummy field is used to initialize the table. You cant create an empty table, as far as I know.
Move the Drop Field Dummy; Statement after the Next statements and it should work (I have fixed it in my original answer).
Sorry Tamil, I missed commas
...
LOAD '$(vCountry)' as Country,
...
Marc.