topic Re: Field Name as colum values : Data model challenge in QlikView

Field Name as colum values : Data model challenge

shekhar_analyti — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Field Name as colum values : Data model challenge

avinashelite — Fri, 17 Feb 2017 08:08:02 GMT

Check this thread

Dynamically Loading Multiple Excel Files

Re: Field Name as colum values : Data model challenge

shekhar_analyti — Fri, 17 Feb 2017 08:17:10 GMT

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

Re: Field Name as colum values : Data model challenge

vinieme12 — Fri, 17 Feb 2017 08:24:25 GMT

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;

Re: Field Name as colum values : Data model challenge

aarkay29 — Fri, 17 Feb 2017 08:25:38 GMT

The Crosstable Load

May be this helps

Re: Field Name as colum values : Data model challenge

vinieme12 — Fri, 17 Feb 2017 08:28:43 GMT

Check the attached

Re: Field Name as colum values : Data model challenge

shekhar_analyti — Fri, 17 Feb 2017 09:58:42 GMT

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

Re: Field Name as colum values : Data model challenge

vinieme12 — Fri, 17 Feb 2017 10:12:55 GMT

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

Try the exact same script with the original Excel file

Re: Field Name as colum values : Data model challenge

vinieme12 — Fri, 17 Feb 2017 14:45:01 GMT

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

Re: Field Name as colum values : Data model challenge

shekhar_analyti — Fri, 17 Feb 2017 14:48:59 GMT

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

Re: Field Name as colum values : Data model challenge

MarcoWedel — Fri, 17 Feb 2017 18:00:43 GMT

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.xlsx] (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

Re: Field Name as colum values : Data model challenge

Peter_Cammaert — Fri, 17 Feb 2017 18:06:03 GMT

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?

Re: Field Name as colum values : Data model challenge

MarcoWedel — Fri, 17 Feb 2017 18:39:25 GMT

Yes, the challenge was a bit vague regarding this topic

Re: Field Name as colum values : Data model challenge

sasiparupudi1 — Sat, 10 Mar 2018 10:39:19 GMT

Please check

Rename Column Names Programatically in the Qlikview Script

Re: Field Name as colum values : Data model challenge

shiveshsingh — Sat, 10 Mar 2018 11:59:55 GMT

This is working , Nice