Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a number of Excel docs in a folder with naming convention of FileName_YYMMDD.
The problem is that the docs may contain a different number of sheets i.e. Doc1 has Sheet1/Sheet2
while Doc2 has Sheet1/Sheet2/Sheet3 etc.
I need to create a table of Doc name and Sheets belonging to that Doc.
Then I want to load the sheets from their associated docs in a loop statement.
Anyone any ideas?
Thanks
I still don't know why but when I create a new field in the new table it stays otherwise it dissappear when drop the original tables.
You can schedule the reload, it will update.
Hi Sajeevan,
The script is as below:
for each sheet in 'Australia', 'China', 'Japan'
CrossTable(Months, Data, 11)
LOAD hier,
class,
ATC3,
[ATC 4],
crp,
[Merge crp],
inprd,
molecule,
FDC,
[Dec-10],
[Jan-11],
[Feb-11],
[Mar-11],
[Apr-11],
[May-11],
[Jun-11],
[Jul-11],
[Aug-11],
[Sep-11],
[Oct-11],
[Nov-11],
[Dec-11],
'$(sheet)' as Country
FROM
(ooxml, embedded labels, table is $(sheet));
next;
You need to move the '$(sheet)' as Country from the end. Find below the changed script.
for each sheet in 'Australia', 'China', 'Japan'
CrossTable(Months, Data, 11)
LOAD hier,
class,
ATC3,
[ATC 4],
crp,
[Merge crp],
inprd,
molecule,
FDC,
'$(sheet)' as Country,
[Dec-10],
[Jan-11],
[Feb-11],
[Mar-11],
[Apr-11],
[May-11],
[Jun-11],
[Jul-11],
[Aug-11],
[Sep-11],
[Oct-11],
[Nov-11],
[Dec-11]
FROM
(ooxml, embedded labels, table is $(sheet));
next;
Regards,
Sajeevan
hi Sajeevan,
the script is uploaded successfully, but still i cant able to get the field "Country"
Please check the attached files
hi, Iam using PE version of the QV so cant able to open your sample file
Find below the load script I have within my sample file. Save the excel file in C:\Temp and paste the below script and load it.
for each sheet in 'Australia', 'China', 'Japan'
CrossTable(Months, Data, 11)
LOAD hier,
class,
ATC3,
[ATC 4],
crp,
[Merge crp],
inprd,
molecule,
FDC,
'$(sheet)' as Country,
[Dec-10],
[Jan-11],
[Feb-11],
[Mar-11],
[Apr-11],
[May-11],
[Jun-11],
[Jul-11],
[Aug-11],
[Sep-11],
[Oct-11],
[Nov-11],
[Dec-11]
FROM
(ooxml, embedded labels, table is $(sheet));
next;
thanks sajeevan now its came, there was a extra column in my excel, thaanks alot, can i ask for a small advice from you, i am a new bee in QV can you suggest me how to improve my skills i know its come by experience bt still any suggestion from your side
I mainly use QlikCommunity to improve my skills by trying to solve the issues the community members have or try the solutions posted in the community. I am coming from a data processing background hence can visualize the database structure. I have experience in restructuring the database to make the analysis easier.
QlikCommunity is the best source to upskill yourself.
Regards,
Sajeevan
K thank alot