topic Re: Show certain orders in QlikView

Show certain orders

Sat, 21 Mar 2015 12:31:24 GMT

Hello

Recently started working with QlikView, but I have trouble with filtering out certain values.

Im looking to return orders where a customer have only bought one type of product (shoes). In the table below, I want to show orders where only shoes is in the order. So it should return OrderID 1, 3 and 4. CAn it be done in the load script?

OrderID	Products
1	Shoes
2	Shoes
2	Hats
3	Shoes
4	Shoes
4	Shoes
5	Pants

thanks in advance.

Re: Show certain orders

fred_s — Sat, 21 Mar 2015 21:13:10 GMT

Hi Karl,

Use something like this:

Select OrderID,

Products

FROM yourtablename

WHERE Products = 'Shoes';

Grtz Fred

Re: Show certain orders

robert_mika — Sat, 21 Mar 2015 22:05:44 GMT

t1:

load * inline

[

OrderID ,Products

1, Shoes

2, Shoes

2, Hats

3, Shoes

4, Shoes

5, Pants

]

;

t2:

load OrderID as ID,

count(OrderID) as CountAll

resident t1

Group by OrderID;

join(t2)

load OrderID as ID,

count(OrderID) as CountShoes

resident t1

where Products ='Shoes'

group by OrderID;

final:

load ID as OrderID

resident t2

where CountAll=CountShoes;

drop table t1,t2

Re: Show certain orders

maxgro — Sun, 22 Mar 2015 07:07:08 GMT

t1:

load * inline [

OrderID ,Products

1, Shoes

2, Shoes

2, Hats

3, Shoes

4, Shoes

5, Pants

];

t2:

load * where NumProdXOrder=1 and MaxProdXOrder='Shoes';

load OrderID, count(distinct Products) as NumProdXOrder, MaxString(Products) as MaxProdXOrder

Resident t1 group by OrderID;

Right Keep (t1) load OrderID Resident t2;

DROP Table t2;

Re: Show certain orders

MK_QSL — Sun, 22 Mar 2015 07:47:02 GMT

T1:

Load *, RowNo() as ID Inline

[

OrderID, Products

1, Shoes

2, Shoes

2, Hats

3, Shoes

4, Shoes

5, Pants

];

Left Join (T1)

Load

OrderID,

Count(Distinct Products) as TotalProduct

Resident T1

Group By OrderID;

Left Join (T1)

Load

OrderID,

1 as Flag

Resident T1

Where TotalProduct = 1 and Products = 'Shoes';

Now Create a TableBox with Flag and OrderID dimension...