topic Re: Coulmn with Max data value in load script in QlikView

Coulmn with Max data value in load script

poonam_kulwal — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Coulmn with Max data value in load script

sunny_talwar — Fri, 18 Jan 2019 13:13:48 GMT

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;

Re: Coulmn with Max data value in load script

vvira1316 — Fri, 18 Jan 2019 13:17:40 GMT

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

Re: Coulmn with Max data value in load script

poonam_kulwal — Sun, 20 Jan 2019 13:00:02 GMT

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.

Re: Coulmn with Max data value in load script

sunny_talwar — Tue, 22 Jan 2019 12:22:41 GMT

Super