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: 
shanu_s9
Contributor III
Contributor III

Loading Data from Pivot table

Hi Experts,

i have data in a pivot table as attached excel file, how to convert as normal load.

How to i load this kind of data, i have to calculate KPI's based on Product A,B &C, highest Market sales in Product A,B & C, Highest Product Sale,... etc.

Kindly some one please help me.

Regards,

Shanu.

Labels (1)
3 Replies
TimvB
Creator II
Creator II

Hi Shanu, you should use the function CrossTable() to unpivot the data. As for the Product Group and the two KPIs, you need some scripting. The below script will work for you to get the data into a straight table:

The resulting straight table is attached as PNG.

 

 

 

ProductCategory_map:
Mapping Load * Inline [
Sales Category, Product Category
s, Product A
1, Product B
2, Product C
];

[Sheet1_tmp]:
CROSSTABLE ([Sales Category],[Sales Amount],6)
LOAD
	[Doctor ID],
	[First Name],
	[Last name],
	[City],
	[Zip],
	[Specialty],
	[Product Sales],
	[Market Sales],
	[Product Sales1],
	[Market Sales1],
	[Product Sales2],
	[Market Sales2]
 FROM [lib://Desktop/Sample Data.xlsx]
(ooxml, embedded labels, header is 1 lines, table is Sheet1);

[Sheet1]:
NoConcatenate Load
	[Doctor ID],
	[First Name],
	[Last name],
	[City],
	[Zip],
	[Specialty],
    ApplyMap('ProductCategory_map',right([Sales Category],1),'N/A')	as [Product Group],
	if(left([Sales Category],1) = 'P',[Sales Amount],null()) 		as [Product Sales]
Resident [Sheet1_tmp] 
Where left([Sales Category],1) = 'P';

Left Join([Sheet1])
Load
	[Doctor ID],
	[First Name],
	[Last name],
	[City],
	[Zip],
	[Specialty],
    ApplyMap('ProductCategory_map',right([Sales Category],1),'N/A')	as [Product Group],
    if(left([Sales Category],1) = 'M',[Sales Amount],null()) 		as [Market Sales]
Resident [Sheet1_tmp]
Where left([Sales Category],1) = 'M';

Drop Table [Sheet1_tmp];

 

 

 

Hope it helps!

shanu_s9
Contributor III
Contributor III
Author

Thanks Tim

It's working...

TimvB
Creator II
Creator II

Great! Please mark the comment as "The Solution" so that others can find it.