Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear Experts,
I have 10-20 Excel sheets data for JUN month again
i have 10-20 Excel sheets data for JULY month, it will get add for all months
here i just want to add all the months data and i want to show month trend
im struck here don't know what technique should i follow
can some one please suggest any idea or your valuable suggestions please
Many Thanks
Niranjan
I need you guys suggestions
Could you please send an example of your data?
Modify and Try below script:
FOR EACH file in FileList('C:\Qlik\*.xlsx');
ODBC CONNECT32 TO [Excel Files;DBQ=$(file)];
Temp:
SQLtables;
DISCONNECT;
FOR i = 0 to NoOfRows('Temp')-1
LET sheetName = peek('TABLE_NAME', i, 'Temp');
Table:
Load * ,
Month(Month) as Month,
MonthName(Month) as MonthName,
FileBaseName()as FIle,
FileDir() as Dir,
FileName() as File_Name,
'$(sheetName)' as Sheet_name
From $(file)(ooxml, embedded labels, table is [$(sheetName)], filters(
Remove(Row, RowCnd(CellValue, 1, StrCnd(null)))));
NEXT i
Drop table Temp;
Next
Final:
Concatenate
LOAD *
Resident Table;
DROP Table Table;
Hey Max,
but its like common fields for every month,June month i will get same fields with June data
July month i will get same fields with July data
(what im doing here every month im copy pasting data into one master file with the same fields)
is there any better approach we will handle this problem
Best Regards,
Niranjan
Another Idea,
Paste the excel files in a folder and create a qvw file with the below script. It will concatenate all the fields.
Consolidation:
LOAD *,
FileName() as FileName,
FilePath() as FilePath
FROM
[*.xlsx]
(ooxml, embedded labels, table is Sheet1);
Let me know, If you need example file.
Hi Nagaraj,
That would be great if can i get QVW
Best Regards,
Niranjan
The approach of neetha is accurate, also if you have some sample you can upload it wold be easier to assist you.
Regards
Hi Neetha,
Im little bit confused using this code can you please provide sample QVW
if can i get from you that would be great help for me
first i will try with sample files then i will implement in my main file
Best Regards,
Niranjan
Hi Niranjan K,
Check the attachment and let me know whether this is fine for you. If not, please post some sample data.