Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All ,
I have been asked in interview about how to handle data in excel sheet which will come in below format :
14-Jan | 14-Feb | 14-Mar | 14-Apr | 14-May | |
Hawai North | 30 | 33 | 36 | 42 | 51 |
Sale of Florals | 10 | 11 | 12 | 14 | 17 |
Bonus | 10 | 11 | 12 | 14 | 17 |
No. of Vendors | 10 | 11 | 12 | 14 | 17 |
Hawai South | 66 | 99 | 132 | 165 | 198 |
Sale of Florals | 22 | 33 | 44 | 55 | 66 |
Bonus | 22 | 33 | 44 | 55 | 66 |
No. of Vendors | 22 | 33 | 44 | 55 | 66 |
Hawai East | 165 | 33 | 186 | 230 | 186 |
Sale of Florals | 55 | 11 | 142 | 142 | 142 |
Bonus | 55 | 11 | 11 | 11 | 11 |
No. of Vendors | 55 | 11 | 33 | 77 | 33 |
Hawai West | 198 | 55 | 179 | 227 | 180 |
Sale of Florals | 55 | 11 | 142 | 142 | 142 |
Bonus | 88 | 33 | 4 | 8 | 5 |
No. of Vendors | 55 | 11 | 33 | 77 | 33 |
Every month data will come for different place (refer attachment) in same format . Please help me with this .
Note : Data model created should not involved manual changes every month
Check this thread
Hi Avinash ,
Thank for the reply . My concern in not the data from multiple places but the pattern of data itself .Please refer data sheet attached .
Thanks
so what is the challenge exactly? I would do the below to concatenate data for each tab and add flag with sheetname to identify the region/location
PS :Also preferably cross tab the fact
For Each vFile in FileList('FullFilePath')
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);
FACT:
LOAD '$(vSheet)' as [LocationName], *
From [$(vFile)]
(ooxml, embedded labels, table is $(vSheet));
Next;
Next;
May be this helps
Check the attached
Hi Veenth ,
Thanks for reply .
I see you have made changes to source data sheet which I shared earlier . But that is not my requirement .
My requirement is that , there should not be any changes done to source data sheet while reading data from it . However , necessary transformation can be done through code or whatever so that eve time when new data sheet is replaced with old one at source location , manual copy paste is not required .
I am attaching pic to highlight difference .
My datasheet
Your Modification
That doesn't make any difference buddy , QV recognizes where the table starts from
Try the exact same script with the original Excel file
shekhar.analytics, have you found your answer? if yes please close the thread by marking a response as correct
Qlik Community Tip: Marking Replies as Correct or Helpful
I am in meeting , will try and mark it correct once I am done .