topic Re: [Subquery] Pick only one row from table that many rows match few conditions in QlikView

[Subquery] Pick only one row from table that many rows match few conditions

adamjank — Thu, 08 Dec 2016 19:03:40 GMT

Hello,

As the SQL oriented user I cannot provide the solution for the QV of getting the same results as I can manage in the SQL Server, so...

I'll be grateful for the solution or the ways to solve it. I want to get the biggest value from the Exc table, but only rows that match the conditions are taken into account; for keeping the transparency of the process I'd like to list the Row Number to show which condition has been matched.

I've provided sample file for the testing an there's the pseudoSQL below that it's the solution for the this problem.
Furthermore I'd like to not use the left join as the one row can match many conditions, so it'll duplicate the data (picking the proper rows from the table with duplicates isn't the best idea). The ApplyMap() won't solve it as the condition sequence has to be chosen (ApplyMap(..,ApplyMap(..,ApplyMap())) etc. for the synthetic keys).

I hope there exists some efficient solution.

Thank you in advance

SELECT

Vegs.Color,

Vegs.Country,

Vegs.Name,

Vegs.Type,

Coalesce((SELECT Top 1 Exc.Value //get the highest Value

FROM Exc

WHERE IIF(Exc.Color = '*', True, Exc.Color=Vegs.Color) AND

IIF(Exc.Country = '*', True, Exc.Country=Vegs.Country) AND

IIF(Exc.Name = '*', True, Exc.Name=Vegs.Name) AND

IIF(Exc.Type = '*', True, Exc.Type=Vegs.Type)

ORDER BY Exc.Value Desc

),0) as Answer.Value,

STUFF(( SELECT ',' + Exc.No //iterate via rows that match the conditions in the Exc table

FROM Exc

WHERE IIF(Exc.Color = '*', True, Exc.Color=Vegs.Color) AND

IIF(Exc.Country = '*', True, Exc.Country=Vegs.Country) AND

IIF(Exc.Name = '*', True, Exc.Name=Vegs.Name) AND

IIF(Exc.Type = '*', True, Exc.Type=Vegs.Type)

ORDER BY Exc.Value Desc

FOR XML PATH('')

), 1, 1, '') as as Answer.No

FROM Vegs

Re: [Subquery] Pick only one row from table that many rows match few conditions

maxgro — Thu, 08 Dec 2016 20:03:37 GMT

The idea here is to join (full outer) the 2 tables and then read the result table to check the rules

Vegs:

LOAD * Inline

[Type, Name, Color, Country

Vege, Cucumber, Green, Spain

Vege, Tomato, Red, Spain

Fruit, Orange, Orange, Turkey

Fruit, Apple, Green, Italy

Vege, Potato, Yellow, Italy

Vege, Cucumber, Green, Italy];

//Exc:

join (Vegs)

LOAD * INLINE

[VNo, VType, VName, VColor, VCountry, VValue

1,*, *, *, Spain, 10

2,*,* , Red,* ,20

3,Fruit, *, *, *,30

4,*, Apple, *,Italy, 40

];

Vegs2:

LOAD

*, -1*((R1+R2+R3+R4)=-4) as R;

NoConcatenate load

Type, Name, Color, Country,

VNo,

(Type=VType or VType='*') as R1,

(Name=VName or VName='*') as R2,

(Color=VColor or VColor='*') as R3,

(Country=VCountry or VCountry='*') as R4,

VValue

Resident Vegs;

DROP Table Vegs;

Vegs3:

LOAD

Type, Name, Color, Country, if(R,VNo) as VNo, if(R, VValue) as VValue

Resident Vegs2

Where Type <> Peek('Type') or Name <> Peek('Name') or Color <> Peek('Color') or Country <> Peek('Country')

Order By Type, Name, Color, Country, R desc, VValue desc;

DROP Table Vegs2;

Re: [Subquery] Pick only one row from table that many rows match few conditions

adamjank — Fri, 09 Dec 2016 08:36:22 GMT

Thank you maxgro‌!
It's working solution, unfortunately I have to make it via joins.

Is it possible to make some kind of synthetic key to keep the Exc Table somehow linked?
I'd like to choose one item in the Vegs3 table and see only the rows with matched conditions.

Thank you in advance

Re: [Subquery] Pick only one row from table that many rows match few conditions

maxgro — Fri, 09 Dec 2016 19:11:53 GMT

maybe:

add the Exc table at the end of my previous script, the association (join) is by VNo

Exc:

LOAD * INLINE

[VNo, VType, VName, VColor, VCountry, Value

1,*, *, *, Spain, 10

2,*,* , Red,* ,20

3,Fruit, *, *, *,30

4,*, Apple, *,Italy, 40

];