topic Re: Pick the data based on the max value. in QlikView

Pick the data based on the max value.

Wed, 25 Nov 2020 16:16:04 GMT

I have 2 tables.

Table 1:

ID Amount Unit

1 100 Office

1 200 Rent

1 300 Warehouse

2 20 Office

2 80 Warehouse

2 100 Rent

Table 2:

ID Unit

1 Office

1 Warehouse

1 Rent

2 Office

2 Warehouse

2 Rent

Here I have to pick the Unit for ID based the Max amount

My desired output should look like below and also I need Unit as a filter..so they can filter the ID's based on the unit.

ID Amount Unit

1 300 Warehouse

2 100 Rent

Thanks much.

Re: Pick the data based on the max value.

sunny_talwar — Thu, 01 Sep 2016 14:33:29 GMT

May be this

Table1:

LOAD ID,

Max(Amount) as Amount,

FirstSortedValue(Unit, -Amount) as Unit

Group By ID;

LOAD * INLINE [

ID, Amount, Unit

1, 100, Office

1, 200, Rent

1, 300, Warehouse

2, 20, Office

2, 80, Warehouse

2, 100, Rent

];

Left Join(Table1)

LOAD * INLINE [

ID, Unit

1, Office

1, WareHouse

1, Rent

2, Office

2, WareHouse

2, Rent

];

Re: Pick the data based on the max value.

Thu, 01 Sep 2016 14:39:37 GMT

Sorry Sunny.My bad.I dont have Unit in Table 1.

Re: Pick the data based on the max value.

sunny_talwar — Thu, 01 Sep 2016 14:42:19 GMT

So how else would do you know that 300 is associated with Unit Warehouse for ID = 1?

Re: Pick the data based on the max value.

Thu, 01 Sep 2016 14:45:42 GMT

based on the ID from Table 2.

Re: Pick the data based on the max value.

sunny_talwar — Thu, 01 Sep 2016 14:47:47 GMT

Both tables have three IDs, how do I know ID 1 where Amount = 300 is associated Warehouse

Re: Pick the data based on the max value.

Thu, 01 Sep 2016 14:50:08 GMT

From Table 1 pick the max amount for each ID and then associate that ID to the Unit in Table2.

Re: Pick the data based on the max value.

Thu, 01 Sep 2016 14:52:13 GMT

I understood your question now..Let me write up the sample again..Thank you.

Re: Pick the data based on the max value.

sunny_talwar — Thu, 01 Sep 2016 14:52:40 GMT

But your table 2 have all three Units

Re: Pick the data based on the max value.

sunny_talwar — Thu, 01 Sep 2016 14:52:53 GMT

Sounds good

Re: Pick the data based on the max value.

Thu, 01 Sep 2016 15:07:44 GMT

Here it is .Thank you.

Tabl1:

ID UnitID Amount

1 OF 100

1 RE 200

1 WH 300

2 OF 200

2 RE 600

2 WH 500

Table2:

UnitID Unit

OF Office

RE Rent

WH Warehouse

Re: Pick the data based on the max value.

trdandamudi — Thu, 01 Sep 2016 15:16:58 GMT

I just took Sunny's code and modified it:

Re: Pick the data based on the max value.

vishsaggi — Thu, 01 Sep 2016 15:30:54 GMT

Sunny's script should work just fine. Another way is as the UnitID is common between two tables autoconcatenation anyway happens here. Probably in the straight table we can get it like

Using pivot table chart.

Dim: ID

Expr as below