Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Master
Master

Keep one row

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?

1 Solution

Accepted Solutions
Highlighted
Specialist II
Specialist II

Re: Keep one row

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;

View solution in original post

5 Replies
Highlighted
Specialist II
Specialist II

Re: Keep one row

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

Highlighted
Master
Master

Re: Keep one row

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.

Highlighted
Specialist II
Specialist II

Re: Keep one row

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;

View solution in original post

Highlighted
Master
Master

Re: Keep one row

Hi,

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

Highlighted
Specialist II
Specialist II

Re: Keep one row

Hi Staffan

You're welcome.

Until next time

Joaquín