Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I have an excel spreadsheet which contains 2 tabs with different customer names as attached. (I will have more tabs for more customers later on)
I want to be able to retrieve the tab names as part of the load script so I can link the spend with the each customers.
I will also need to use crosstable wizard so Qlikview can ready it.
I will also need a way to convert the date to date format as QlikView is currently reading it as string
The number of vendors will varies as each customer is different and has a different vendor list.
How can I do this?
Hi,
Try this Script:
directory;
For Each vFile in FileList('FY1415_Report.xlsx')
ODBC CONNECT32 To [Excel Files;DBQ=$(vFile)];
Sheets:
SQLTABLES;
DISCONNECT;
For i = 0 To NoOfRows('Sheets')-1
Let vSheet = left(Peek('TABLE_NAME', i, 'Sheets'), len(Peek('TABLE_NAME', i, 'Sheets'))-1);
CustomerTable:
CrossTable(Date, Data, 3)
LOAD '$(vSheet)' as [Tab Name],
VENDOR,
TYPE,
[41821],
[41852],
[41883],
[41913],
[41944],
[41974],
[42005],
[42036],
[42064],
[42095],
[42125],
[42156]
FROM
[$(vFile)]
(ooxml, embedded labels, table is $(vSheet))
WHERE(not IsNull(VENDOR));
Next;
DROP Table Sheets;
Next;
Final:
LOAD [Tab Name],
VENDOR,
TYPE as [Project Type],
Date(Num#(Date)) AS Reporting_Date,
Date(Floor(Num#(Date)),'MMM-YY') as MonthYear,
Data
Resident CustomerTable;
Drop Table CustomerTable;
Hi,
Try this Script:
directory;
For Each vFile in FileList('FY1415_Report.xlsx')
ODBC CONNECT32 To [Excel Files;DBQ=$(vFile)];
Sheets:
SQLTABLES;
DISCONNECT;
For i = 0 To NoOfRows('Sheets')-1
Let vSheet = left(Peek('TABLE_NAME', i, 'Sheets'), len(Peek('TABLE_NAME', i, 'Sheets'))-1);
CustomerTable:
CrossTable(Date, Data, 3)
LOAD '$(vSheet)' as [Tab Name],
VENDOR,
TYPE,
[41821],
[41852],
[41883],
[41913],
[41944],
[41974],
[42005],
[42036],
[42064],
[42095],
[42125],
[42156]
FROM
[$(vFile)]
(ooxml, embedded labels, table is $(vSheet))
WHERE(not IsNull(VENDOR));
Next;
DROP Table Sheets;
Next;
Final:
LOAD [Tab Name],
VENDOR,
TYPE as [Project Type],
Date(Num#(Date)) AS Reporting_Date,
Date(Floor(Num#(Date)),'MMM-YY') as MonthYear,
Data
Resident CustomerTable;
Drop Table CustomerTable;
Hi,
Thanks for this! it works!
If I need to load multiple excel spreadsheet with the same format but different date on the top. (e.g. another file for FY1314_Report)
Do I need to create another load table and the final table?
Hope you can assist with this one as well.
Thank you
Can I also ask why you set the qualifier fields to 3 not 2? Thanks
Sorry that was the wrong attachment, can you please use this one as example (This one has different date)
Because, I Load the '$(vSheet)' as 1st Field. That's why i used 3 as a qualifier. You can remove it, if you don't want.
In the Meantime, you can try to Load your fields with 'no labels' instead of Embedded Labels.
finally , you can change the Field Name..
Hi Susan,
Find Attached the Updated Version..
Thank you!