Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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.

3 Replies
Highlighted
Creator II
Creator II

Re: Loading Data from Pivot table

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!

Highlighted
Contributor III
Contributor III

Re: Loading Data from Pivot table

Thanks Tim

It's working...

Highlighted
Creator II
Creator II

Re: Loading Data from Pivot table

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