Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Announcement: Certain actions are currently causing a Page Not Found error. This is a known issue and we are working with the platform vendor to investigate and resolve it.
Frank_Hartmann
Honored Contributor

How can I get this excel data into Qlikview?

Hi Experts,

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

Unbenannt.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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

Unbenannt.png

Labels (2)
2 Solutions

Accepted Solutions

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

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;
mwoolf
Honored Contributor II

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

Try the attached qvw.

6 Replies
Claudiu_Anghelescu
Contributor III

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

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

To help community find solutions, please don't forget to mark as correct.
Frank_Hartmann
Honored Contributor

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

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?

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;
mwoolf
Honored Contributor II

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

Try the attached qvw.

Frank_Hartmann
Honored Contributor

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

thank you very much 🙂

Highlighted
Frank_Hartmann
Honored Contributor

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

thank you very much 🙂