Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Frank_Hartmann
Master II
Master II

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
sunny_talwar

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;

View solution in original post

m_woolf
Master II
Master II

Try the attached qvw.

View solution in original post

6 Replies
Claudiu_Anghelescu
Specialist
Specialist

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
Master II
Master II
Author

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

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
Master II
Master II

Try the attached qvw.

Frank_Hartmann
Master II
Master II
Author

thank you very much 🙂

Frank_Hartmann
Master II
Master II
Author

thank you very much 🙂