topic Re: Keep one row in QlikView

Keep one row

stabben23 — Mon, 25 Apr 2016 14:19:24 GMT

This is my Query:

select min(t2.Price) as Price, t2.RuleSet, t1.BoardID, t1.Grading from [dbo].[tblLimitingRules] as t1

left join [dbo].[tblProducts] as t2 on

t1.RuleSetID=t2.RuleSet

where

t2.RuleSet <> ''

and t1.Grading = 1 and

t1.BoardID ='19060205'

group by t1.BoardID, t1.Grading, t2.RuleSet

order by t1.BoardID asc

This is my result:

Price RuleSet BoardID Grading

1900.00 644 19060205 1

2500.00 691 19060205 1

1475.00 692 19060205 1

I just want to keep the row With the lowest Price on all BoardID, this is just one of ~ 18 million BoardID and they have different RuleSet and Price.

Is there any way in sql or qlikview to solve this?

Re: Keep one row

Joaquin_Lazaro — Mon, 25 Apr 2016 14:31:14 GMT

Try this

select min(t2.Price) as Price, t1.BoardID from [dbo].[tblLimitingRules] as t1

left join [dbo].[tblProducts] as t2 on

t1.RuleSetID=t2.RuleSet

where

t2.RuleSet <> ''

and t1.Grading = 1 and

t1.BoardID ='19060205'

group by t1.BoardID,

order by t1.BoardID asc

Then you need a new left join to T1 with Price and BoardID to get RuleSet and Grading

Hope this helps

Re: Keep one row

stabben23 — Mon, 25 Apr 2016 14:41:23 GMT

That will not fix the problem, Left join on BoardID will still gives me Three rows here, Price is not a Field from T1.

Grading is not really important here, it is to get correct RuleSet so I can use that as a New key to other table.

Re: Keep one row

Joaquin_Lazaro — Mon, 25 Apr 2016 14:50:07 GMT

You're right.

Then

Temp:

LOAD *,

BoardID & '+' & Price as key.

select min(t2.Price) as Price, t1.BoardID from [dbo].[tblLimitingRules] as t1

left join [dbo].[tblProducts] as t2 on

t1.RuleSetID=t2.RuleSet

where

t2.RuleSet <> ''

and t1.Grading = 1 and

t1.BoardID ='19060205'

group by t1.BoardID,

order by t1.BoardID asc

Table:

LOAD *,

BoardID & '+' & Price as key.

Where Exists(key)

select min(t2.Price) as Price, t2.RuleSet, t1.BoardID, t1.Grading from [dbo].[tblLimitingRules] as t1

left join [dbo].[tblProducts] as t2 on

t1.RuleSetID=t2.RuleSet

where

t2.RuleSet <> ''

and t1.Grading = 1 and

t1.BoardID ='19060205'

group by t1.BoardID, t1.Grading, t2.RuleSet

order by t1.BoardID asc

Drop Table Temp;

Re: Keep one row

stabben23 — Tue, 26 Apr 2016 05:59:46 GMT

Hi,

yes this point me into the correct direction, some small adjustment fix the problem, thanks.

Re: Keep one row

Joaquin_Lazaro — Tue, 26 Apr 2016 06:31:09 GMT

Hi Staffan

You're welcome.

Until next time

Joaquín