Join + where statement

[carolin01]

Hello,

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

1.

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

[jagan]

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.

[shreyashetty_ge]

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

[carolin01]

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?

[#5]

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

[m_bardun · Accepted solution]

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

[#7]

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

[Gysbert_Wassenaar]

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);

[carolin01]

Many thanks for your help. This is the correct answer

[m_bardun]

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"?