Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
(
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
How about this?
fSalesTransactionLoad_tmp:
LOAD
Company,
FiscalPeriodYear,
FiscalPeriod,
OrderPhase,
Item,
OrderDate
FROM
(
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
fSalesTransactionLoad:
LOAD
Company,
FiscalPeriodYear,
FiscalPeriod,
OrderPhase,
Item,
OrderDate,
FROM
(
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.
Hi try this
fSalesTransactionLoad:
LOAD
Company,
FiscalPeriodYear,
FiscalPeriod,
OrderPhase,
Item,
OrderDate
FROM
(
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
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?
Try This,
fSalesTransactionLoad:
LOAD
Company,
FiscalPeriodYear,
FiscalPeriod,
OrderPhase,
Item,
OrderDate
FROM
(
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
How about this?
fSalesTransactionLoad_tmp:
LOAD
Company,
FiscalPeriodYear,
FiscalPeriod,
OrderPhase,
Item,
OrderDate
FROM
(
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
fSalesTransactionLoad:
LOAD
Company,
FiscalPeriodYear,
FiscalPeriod,
OrderPhase,
Item,
OrderDate
FROM
(
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
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
Many thanks for your help. This is the correct answer
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"?