Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
poonam_kulwal
Partner
Partner

Coulmn with Max data value in load script

DataImage.pngDataImage.png

Hi All,

I have sample data as shown below.

I am trying to achieve is to get last column shown as “Max Quty Sold”.

I have first 4 columns in my data set and I am looking for to generate last column as max of quty sold based on Product, Date and Category.

I want this kind of table on load script side so that I can use last column in my calculations across various charts.

Any help will be appreciated.

Thanks

Poonam

 

1 Solution

Accepted Solutions
sunny_talwar

Try this

Table:
LOAD Product, 
     Date, 
     Category, 
     [Quty sold]
FROM
[..\..\Downloads\Data_Sample.xlsx]
(ooxml, embedded labels, table is Sheet1);

Left Join (Table)
LOAD Product,
	 Date,
	 Category,
	 Max([Quty sold]) as [Max Quty Sold]
Resident Table
Group By Product, Date, Category;

View solution in original post

4 Replies
sunny_talwar

Try this

Table:
LOAD Product, 
     Date, 
     Category, 
     [Quty sold]
FROM
[..\..\Downloads\Data_Sample.xlsx]
(ooxml, embedded labels, table is Sheet1);

Left Join (Table)
LOAD Product,
	 Date,
	 Category,
	 Max([Quty sold]) as [Max Quty Sold]
Resident Table
Group By Product, Date, Category;

View solution in original post

vvira1316
Specialist II
Specialist II

NoConcatenate
[DataTable]:
LOAD Product,
Date,
Category,
[Quty sold]
FROM
[Data_Sample.xlsx]
(ooxml, embedded labels, table is Sheet1);

Left Join ([DataTable])
LOAD Product,
Date,
Category,
Max([Quty sold]) as [Max Qty Sold]
Resident [DataTable]
Group By Product,
Date,
Category

 

 

Qty.PNG

poonam_kulwal
Partner
Partner
Author

Thank you Sunny!.

This is very helpful.

However there was also issue with my Date column (in my actual data set). Dates were not interpreted correctly. That leads to not getting desire output with  Group by clause and Left join. 

Issue is now resolved.

 

sunny_talwar

Super