topic Re: Re: Exclude specific records during load in QlikView

Exclude specific records during load

Mon, 24 Nov 2014 20:38:38 GMT

Hello,

I have a large P&L database and would like to exclude specific records during the load.

I have created a similar scenario in the attached qvw using inline load.

In this example I would like to exclude loading records only where CATEGORY is Chocolate and MONTHID is >=10

I tried doing a resident load and using WHERE function to exclude the 2 criteria's. However, this did not work.

Can anyone help please.

Thanks.

Re: Exclude specific records during load

jpapador — Mon, 24 Nov 2014 20:45:12 GMT

PFA

Re: Exclude specific records during load

Mon, 24 Nov 2014 21:03:47 GMT

Thanks for the response, however, this is not what I want to do.

My data load has around 3 million+ records. During data load I want to be able to exclude records only where CATEGORY is Chocolate and MONTHID is >=10. The reason for this is because this category has been divested/sold to another organization from MONTHID 10 onwards.. Therefore, in our P&L statement, we still want to account for that CATEGORY before MONTHID 10 but not after.

Re: Exclude specific records during load

Mon, 24 Nov 2014 21:10:34 GMT

where

If(MonthID>=10 and WildMatch([SKU Description],'Choc*')=1,1,2)=2;

Put this in where condition of fact table.

Re: Re: Exclude specific records during load

jpapador — Mon, 24 Nov 2014 21:13:57 GMT

I revised it a bit. I think this is what you are looking for.

Re: Exclude specific records during load

Mon, 24 Nov 2014 21:17:29 GMT

You could join the tables and then do a resident load:

FactTable:

LOAD * INLINE [

SKU#, SKU Description, Measure, MonthID, Actual Amount

1, Choc Milk, Revenue, 1, 100

1, Choc Milk, Revenue, 10, 100

1, Choc Milk, Costs, 5, 50

1, Choc Milk,Costs, 11, 50

1, Choc Syrup, Revenue, 1, 100

1, Choc Syrup, Revenue, 5, 100

1, Choc Syrup, Costs, 1, 50

2, Ice Cream Sundae, Revenue, 1, 100

2, Ice Cream Vanilla, Revenue, 1, 100

3, Lemonade, Revenue, 2, 500

3, Lemonade, Costs, 2, 100

3, Lemonade, Costs, 2, 200

3, Orange, Revenue, 2, 500

3, Orange, Revenue, 11, 300

];

Outer Join(FactTable)

LOAD * INLINE [

SKU#, Category

1, Chocolate

2, Ice Cream

3, Drinks

];

NoConcatenate

FinalTable:

LOAD * Resident FactTable

Where If(Category = 'Chocolate' and MonthID < 10, 1,0) =0;

Drop Table FactTable;

Re: Exclude specific records during load

MarcoWedel — Mon, 24 Nov 2014 22:18:18 GMT

Hi,

another solution could be:

Product:

LOAD * INLINE [

SKU#, Category

1, Chocolate

2, Ice Cream

3, Drinks

];

mapProduct:

Mapping LOAD * Resident Product;

FactTable:

LOAD *, ApplyMap('mapProduct',SKU#) as test INLINE [

SKU#, SKU Description, Measure, MonthID, Actual Amount

1, Choc Milk, Revenue, 1, 100

1, Choc Milk, Revenue, 10, 100

1, Choc Milk, Costs, 5, 50

1, Choc Milk,Costs, 11, 50

1, Choc Syrup, Revenue, 1, 100

1, Choc Syrup, Revenue, 5, 100

1, Choc Syrup, Costs, 1, 50

2, Ice Cream Sundae, Revenue, 1, 100

2, Ice Cream Vanilla, Revenue, 1, 100

3, Lemonade, Revenue, 2, 500

3, Lemonade, Costs, 2, 100

3, Lemonade, Costs, 2, 200

3, Orange, Revenue, 2, 500

3, Orange, Revenue, 11, 300

]

Where not(ApplyMap('mapProduct',SKU#)='Chocolate' and MonthID>=10);

hope this helps

regards

Marco

Re: Exclude specific records during load

Mon, 24 Nov 2014 22:19:27 GMT

Thank you JaroJaro. That worked !!

It's pretty much what I tried to do but just couldn't get the WHERE function correct - didn't know you could use the IF with it.

Re: Re: Exclude specific records during load

MarcoWedel — Mon, 24 Nov 2014 22:24:03 GMT

Where not(Category = 'Chocolate' and MonthID >= 10);

Where Category<>'Chocolate' or MonthID<10;

should work also

hope this helps

regards

Marco