Nested IF statement in the load script

[guya]

Dea All,

I would like to create a new field in a table during the load script based on the following:

TableA

Products OriginCountry DestinationCountry Price Supplier Createdate Tax

A UK DE 100 DEX 07-12-2010 16

Table B

OriginCountry DestinationCountry Supplier Startdate EndDate Promo

UK DE DEX 01-12-2010 15-01-2011 -9

What I would like is to create a new field FinalPrice in Table A if :

if product = A and OriginCountry = UK and DestinationCountry = DE and Supplier = DEX and Createdate is between table B StartDate & EndDate then apply 9% of promo on the price.

Thank you in advance for your help.

Guy

[#2]

Which field do you use to join the two tables? Concatenation of Origin, Destination, and Supplier?

[guya]

I'm usining the DestinationCountry.

Thx

Guy

[#4]

Dear Guy ,

you can do the following

TableA

Products OriginCountry DestinationCountry Price Supplier Createdate Tax

A UK DE 100 DEX 07-12-2010 16

Join

OriginCountry DestinationCountry Supplier Startdate EndDate Promo

UK DE DEX 01-12-2010 15-01-2011 -9

Load

Price as PromoPrice ,

OriginCountry

resident Table1

where Product ='A' and OrginCountry='UK' and Destination Country ='DE' and Supplier ='DEX' and CreateDate >= StartDate and CreateDate <= EndDate .

Either you can join the table or be it as a seperate table ...

You can use the Promo Price and add the 9 % to it or you can do it using groupby funcion in the same table ..

Hope this helps you ...

Regards,

Chakravarthy.

[guya]

Hello,

That doesn't help me to create an additional field in TableA or I miss something (I'm new in QV)

Also, the product is the reference for the conditions. So tI should get something like this:

If the product-OriginCountry= 'UK' and product-DestinationCountry= 'DE'....etc then Product-price * 0.09 and store it in FinalPrice.

thx

Guy

[#6]

This seems to work for me...

The basic idea to to combine both tables into one to using the left join. If you want to keep both A & B seperate, only load the A fields (plus discounted price) in the RESIDENT Load. Then drop the temp table, next load Table B again. In the below example I am keeping both A & B as one table.

Hope this helps...>

tempTableA:
LOAD * INLINE [
Products, OriginCountry, DestinationCountry, Price, Supplier, Createdate, Tax
A, UK, DE, 100, DEX, 2010-7-12, 16
];

LEFT JOIN (tempTableA)
LOAD * INLINE [
OriginCountryB, DestinationCountry, SupplierB, StartDate, EndDate, Promo
UK, DE, DEX, 2010-1-12, 2011-1-15, -9
];


TableAB:
LOAD
Products
,OriginCountry
,DestinationCountry
,Price
,Supplier
,date#(date(Createdate,'MM-DD-YY')) as Createdate
,Tax
,OriginCountryB
//,DestinationCountry
,SupplierB
,date#(date(StartDate,'MM-DD-YY')) as StartDate
,date#(date(EndDate,'MM-DD-YY')) as EndDate
,Promo
//
// if product = A and OriginCountry = UK and DestinationCountry = DE and Supplier = DEX and Createdate is between table B StartDate & EndDate then apply 9% of promo on the price.

,if(Products='A' And OriginCountry='UK' and DestinationCountry = 'DE' and Supplier = 'DEX' and Createdate >= StartDate and Createdate <= EndDate, Price + (Price * Promo / 100),Price) as DisountedPrice

RESIDENT tempTableA
;

DROP TABLE tempTableA;

[#7]

Its not a perfect solution but if Table B is "small" it should be descent. Could be that you need to add a trim inside the date#-functions.

temp:
noconcatenate load * resident TableA;
left join load * resident TableB;

drop table TableA;

TableA:
load Products,OriginCountry, DestinationCountry,Supplier,Createdate,Tax,Price
, if(date#(Startdate,'DD-MM-YYYY')<=date#(Createdate,'DD-MM-YYYY') and date#(Createdate,'DD-MM-YYYY')<=date#(EndDate,'DD-MM-YYYY'), Price * (100+Promo)/100,Price ) as FinalPrice

resident temp;
drop table temp;