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

[MarcoWedel]

Hi,

maybe way to load your data without using hard coded date column names might be:

table1:
CrossTable (DateTemp, Value,2)
LOAD RowNo() as ID,* FROM [https://community.qlik.com/servlet/JiveServlet/download/1214965-266186/Column%20as%20field%20values....] (ooxml, embedded labels, table is Hawai);

Join
LOAD Distinct
     DateTemp,
     Date(Num#(DateTemp),'DD-MMM YY') as Date
Resident table1;

DROP Field DateTemp;

RENAME Field F1 to Type;

hope this helps

regards

Marco

[Peter_Cammaert]

Now the next challenge seems to be to eliminate the totals lines but keep the header value and add this as an extra field to the next three lines. Good exercise for the OP?

[MarcoWedel]

Yes, the challenge was a bit vague regarding this topic

[sasiparupudi1]

Please check

Rename Column Names Programatically in the Qlikview Script

[shiveshsingh]

This is working , Nice