topic Re: Join + where statement in QlikView

Join + where statement

carolin01 — Fri, 11 Oct 2013 09:32:34 GMT

Hello,

I´m new to Qlik View and have some questions on an issue I´m working on. I´ve created two load statements:

fSalesTransactionLoad:

LOAD
     Company,
     FiscalPeriodYear,
     FiscalPeriod,
     OrderPhase,
     Item,
     OrderDate,
FROM

(qvd);
Where???

2.

PrelimenarydProducts:
left join (fSalesTransactionLoad)
LOAD
     H1I as LineOfBusiness,
     Item
Resident vdProducts_ItemGroup;

After having joined the field LineOfBusiness into the table fSalesTransactionLoad I would like to reduce the data I load there. I do not want to load data where the OrderPhase is 3 and the field LineOfBusiness is "Freight" or "Packaging". I tried join the data from the PrelimenarydProducts with a right join first. But Qlik View told me that it could not find the table fSalesTransactionLoad. And if I do a left join then I cannot implement a where statement for the field LineOfBusiness which is joind afterwards. Also I´m unsure how the where statement should look like. Which is the best way to do this? Any ideas?

Best regards

Carolin

Re: Join + where statement

jagan — Fri, 11 Oct 2013 09:49:59 GMT

fSalesTransactionLoad:

LOAD
     Company,
     FiscalPeriodYear,
     FiscalPeriod,
     OrderPhase,
     Item,
     OrderDate,
FROM

(qvd)
Where OrderPhase <> 3;

left join (fSalesTransactionLoad)
LOAD
     H1I as LineOfBusiness,
     Item
Resident vdProducts_ItemGroup;

WHERE Match(LineOfBusiness, 'Freight', 'Packaging');

Hope this helps you.

Regards,

Jagan.

Re: Join + where statement

shreyashetty_ge — Fri, 11 Oct 2013 09:54:31 GMT

Hi try this

fSalesTransactionLoad:

LOAD
     Company,
     FiscalPeriodYear,
     FiscalPeriod,
     OrderPhase,
     Item,
     OrderDate
FROM

(qvd)
Where OrderDate=3;

left join

LOAD
H1I as LineOfBusiness,
Item
Resident vdProducts_ItemGroup

where LineOfBusiness = "Freight" or LineOfBusiness ="Packaging";

Drop table vdProducts_ItemGroup; //*** if you wish to drop it

Re: Join + where statement

carolin01 — Fri, 11 Oct 2013 11:31:30 GMT

This is close and I appreciate your help. I adjusted it a little bit:

inner join (fSalesTransactionLoad)
LOAD
H1I as LineOfBusiness,
Item
Resident vdProducts_ItemGroup
where not WildMatch (H1I, 'Freight', 'Packing');

Now the fSalesTransactionLoad table shows the LineOfBusiness field. But it doesn´t show freight and packaging at all. It should show all values for LineOf Business for order phase 1 and 2 but not for 3. Only for order phase 3 I don´t want to see freight and packaging. Maybe you´ve a solution for this?

Re: Join + where statement

Fri, 11 Oct 2013 11:38:22 GMT

Try This,

fSalesTransactionLoad:

LOAD
     Company,
     FiscalPeriodYear,
     FiscalPeriod,
     OrderPhase,
     Item,
     OrderDate
FROM

(qvd)
Where OrderDate<>3;

left join

LOAD
H1I as LineOfBusiness,
Item
Resident vdProducts_ItemGroup

where LineOfBusiness = "Freight" or LineOfBusiness ="Packaging";

Drop table vdProducts_ItemGroup; //*** if you wish to drop it

Re: Join + where statement

m_bardun — Fri, 11 Oct 2013 12:00:03 GMT

How about this?

fSalesTransactionLoad_tmp:

LOAD
     Company,
     FiscalPeriodYear,
     FiscalPeriod,
     OrderPhase,
     Item,
     OrderDate
FROM

(qvd);

left join (fSalesTransactionLoad_tmp)
LOAD
H1I as LineOfBusiness,
Item
Resident vdProducts_ItemGroup;

fSalesTransactionLoad:

noconcatenate LOAD

     Company,
     FiscalPeriodYear,
     FiscalPeriod,
     OrderPhase,
     Item,
     OrderDate,

LineOfBusiness

Resident fSalesTransactionLoad_tmp

where not (Match (LineOfBusiness, 'Freight', 'Packaging') and OrderPhase=3);

drop table fSalesTransactionLoad;

Drop table vdProducts_ItemGroup; //*** if you wish to drop it

Re: Join + where statement

Fri, 11 Oct 2013 12:04:53 GMT

fSalesTransactionLoad:

LOAD
     Company,
     FiscalPeriodYear,
     FiscalPeriod,
     OrderPhase,
     Item,
     OrderDate
FROM

(qvd)
Where OrderPhase<>3;

left join

LOAD
H1I as LineOfBusiness,
Item
Resident vdProducts_ItemGroup

where H1l= "Freight" or H1l="Packaging";

Drop table vdProducts_ItemGroup; //*** if you wish to drop it

Re: Join + where statement

Gysbert_Wassenaar — Fri, 11 Oct 2013 12:09:25 GMT

Maybe like this:

MapLoB:

mapping load H1l, Item

resident vdProducts_ItemGroup;

SalesTransactionLoad:

LOAD * where OrderDate<>3 and not WildMatch (LineOfBusiness, 'Freight', 'Packing');

LOAD
     Company,
     FiscalPeriodYear,
     FiscalPeriod,
     OrderPhase,
     Item,

applymap('MapLoB',Item) as LineOfBusiness,
OrderDate
FROM
(qvd);

Re: Join + where statement

carolin01 — Fri, 11 Oct 2013 13:14:02 GMT

Many thanks for your help. This is the correct answer

Re: Join + where statement

m_bardun — Fri, 11 Oct 2013 13:21:24 GMT

Order phase 2 shouldn't be affected.

If you only load the joined tmp-table, can you see that there are rows with order phase 2 and line of business "freight" and/or "packaging"?

Re: Join + where statement

carolin01 — Fri, 11 Oct 2013 13:50:51 GMT

Sorry, this was my fault. I got mixed up with the order phases. Your answer is correct!!!