Exclude specific records during load

[#1]

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.

[jpapador]

PFA

[#3]

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.

[#4]

where

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

Put this in where condition of fact table.

[jpapador]

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

[#6 · Accepted solution]

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

    2, Ice Cream Vanilla, 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;

[MarcoWedel]

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
    2, Ice Cream Vanilla, 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

[#8]

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.

[MarcoWedel]

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

or

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

should work also

hope this helps

regards

Marco