Field Name as colum values : Data model challenge

[shekhar_analyti]

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

[avinashelite]

Check this thread

Dynamically Loading Multiple Excel Files

[shekhar_analyti]

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

[vinieme12]

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;

[aarkay29]

The Crosstable Load

May be this helps

[vinieme12]

Check the attached

[shekhar_analyti]

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

[vinieme12]

That doesn't make any difference buddy , QV recognizes where the table starts from

Try the exact same script with the original Excel file

[vinieme12]

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

[shekhar_analyti]

I am in meeting , will try and mark it correct once I am done .