topic Re: How can I get this excel data into Qlikview? in QlikView

How can I get this excel data into Qlikview?

Frank_Hartmann — Sat, 16 Nov 2024 03:46:29 GMT

Hi Experts,

I have an excelreport on one sheet like below for about 2000 Outlets:

how can i load the data into qlikview to get following output? :

Re: How can I get this excel data into Qlikview?

Claudiu_Anghelescu — Wed, 24 Apr 2019 15:39:20 GMT

See the attached work and let me know if help you.

Re: How can I get this excel data into Qlikview?

Frank_Hartmann — Wed, 24 Apr 2019 16:46:09 GMT

like i wrote above, i have about 2000 Outlets in my Report on one sheet, not only 4.

Your script is not handy enough to cover all the 2000 Outlets at once.

It would be very hard to manually add all the Outlets to the script.

Re: How can I get this excel data into Qlikview?

sunny_talwar — Wed, 24 Apr 2019 17:05:09 GMT

Try something like this

AllTable:
LOAD RowNo() as RowNum,
	 A, 
     B,
     If(WildMatch(A, 'Outlet*') or Len(Trim(A)) = 0, A, Peek('A_New')) as A_New
FROM [..\..\Downloads\Example.xlsx]
(ooxml, no labels, table is INPUT);

Table:
NoConcatenate
LOAD *
Resident AllTable
Where not(WildMatch(A, 'Outlet*') or Len(Trim(A)) = 0);

DROP Table AllTable;

FinalTable:
LOAD DISTINCT A 
Resident Table;

FOR i = 1 to FieldValueCount('A_New')

	LET vOutlet = FieldValue('A_New', $(i));
	
	Left Join (FinalTable)
	LOAD A,
		 B as [Job Counter $(vOutlet)]
	Resident Table
	Where A_New = '$(vOutlet)';
	
NEXT

DROP Table Table;

Re: How can I get this excel data into Qlikview?

m_woolf — Wed, 24 Apr 2019 17:52:11 GMT

Try the attached qvw.

Re: How can I get this excel data into Qlikview?

Frank_Hartmann — Wed, 24 Apr 2019 18:12:19 GMT

thank you very much 🙂

Re: How can I get this excel data into Qlikview?

Frank_Hartmann — Wed, 24 Apr 2019 18:14:41 GMT

thank you very much 🙂