How can I get this excel data into Qlikview?

[Frank_Hartmann]

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? :

[Claudiu_Anghelescu]

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

[Frank_Hartmann]

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.

[sunny_talwar · Accepted solution]

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;

[m_woolf · Accepted solution]

Try the attached qvw.

[Frank_Hartmann]

thank you very much 🙂

[Frank_Hartmann]

thank you very much 🙂