topic Re: IF statement in Script in QlikView

IF statement in Script

Tue, 12 Jun 2012 11:10:01 GMT

I am currently doing the following as a Dimension in a chart, how Do I get this into a script?

Transactions is one table, ABICode is another table

=if(Transactions.ProductID = '33', Num(ABICode.ABIVixaInsuranceGroup),

if(Transactions.ProductID = '31', Num(ABICode.ABIBikeInsuranceGroup),Num(ABICode.ABIAxaInsuranceGroup)))

So in the script I would want to say something like - but i'm not sure where to put into the script.

if(Transactions.ProductID = '33', Num(ABICode.ABIVixaInsuranceGroup),

if(Transactions.ProductID = '31', Num(ABICode.ABIBikeInsuranceGroup),Num(ABICode.ABIAxaInsuranceGroup))) AS NewAbiInsurance Group

Re: IF statement in Script

Tue, 12 Jun 2012 11:14:54 GMT

Try something like this:

Data:

Load *,

if(ProductID = '33', ABIVixaInsuranceGroup,

if(ProductID = '31', ABIBikeInsuranceGroup,

ABIAxaInsuranceGroup

)) as NewAbiInsuranceGroup;

Load ProductID,

Num(ABIVixaInsuranceGroup) as ABIVixaInsuranceGroup,

Num(ABIBikeInsuranceGroup) as ABIBikeInsuranceGroup,

Num(ABIAxaInsuranceGroup) as ABIAxaInsuranceGroup,

Field2,

Field3;

SQL Select ProductID,

ABIVixaInsuranceGroup,

ABIBikeInsuranceGroup,

ABIAxaInsuranceGroup,

Field2,

Field3

From Table1;

Re: IF statement in Script

Tue, 12 Jun 2012 11:25:42 GMT

Am I suppossed to change the name Table1? as this produces an error when I add to script.

Kev.

Re: IF statement in Script

Tue, 12 Jun 2012 11:26:58 GMT

Yes, you must use the correct SQL sentence, i.e., field names, table names, etc.

Re: IF statement in Script

Tue, 12 Jun 2012 11:29:16 GMT

These are tables created in Qlikview not SQL.

Re: IF statement in Script

Tue, 12 Jun 2012 11:30:52 GMT

In this case:

Data:

Load *,

if(ProductID = '33', ABIVixaInsuranceGroup,

if(ProductID = '31', ABIBikeInsuranceGroup,

ABIAxaInsuranceGroup

)) as NewAbiInsuranceGroup;

Load ProductID,

Num(ABIVixaInsuranceGroup) as ABIVixaInsuranceGroup,

Num(ABIBikeInsuranceGroup) as ABIBikeInsuranceGroup,

Num(ABIAxaInsuranceGroup) as ABIAxaInsuranceGroup,

Field2,

Field3

Resident QVTable1;

Re: IF statement in Script

Tue, 12 Jun 2012 11:42:17 GMT

Thanks for your help Enrique, I now get the message -

Field not found - <ProductID>

Load ProductID,

Num(ABIVixaInsuranceGroup) as ABIVixaInsuranceGroup,

Num(ABIBikeInsuranceGroup) as ABIBikeInsuranceGroup,

Num(ABIAxaInsuranceGroup) as ABIAxaInsuranceGroup

Re: IF statement in Script

Anonymous — Tue, 12 Jun 2012 11:52:00 GMT

Try:

Data:

Load *,

if(Transactions.ProductID = '33', ABIVixaInsuranceGroup,

if(Transactions.ProductID = '31', ABIBikeInsuranceGroup,

ABIAxaInsuranceGroup

)) as NewAbiInsuranceGroup;

Load Transactions.ProductID,

Num(ABIVixaInsuranceGroup) as ABIVixaInsuranceGroup,

Num(ABIBikeInsuranceGroup) as ABIBikeInsuranceGroup,

Num(ABIAxaInsuranceGroup) as ABIAxaInsuranceGroup,

Field2,

Field3

Resident QVTable1;

Re: IF statement in Script

Tue, 12 Jun 2012 12:04:27 GMT

I am obviously not doing something right,

If I use Resident Transactions;

It finds Transactions.ProductID as this exists in Transactions Table but then fails on ABIVixaInsuranceGroup

If I use Resident ABICode I get this -

Field not found - <Transactions.ProductID>

Load Transactions.ProductID,

Num(ABIVixaInsuranceGroup) as ABIVixaInsuranceGroup,

Num(ABIBikeInsuranceGroup) as ABIBikeInsuranceGroup,

Num(ABIAxaInsuranceGroup) as ABIAxaInsuranceGroup,

Field2,

Field3

Resident ABICode

Re: IF statement in Script

Tue, 12 Jun 2012 13:12:34 GMT

¿The primary key of ABICode table is ProductID?

Re: IF statement in Script

Anonymous — Tue, 12 Jun 2012 13:13:01 GMT

Mabye a typo?

Do you have a field named [Transactions.ProductID] and is your table namend [ABICode] ?

Re: IF statement in Script

Wed, 13 Jun 2012 09:44:55 GMT

I have a Field named ProductID in Transactions Table

I have a three fields in ABICode Table ABIVixaInsuranceGroup, ABIAxaInsuranceGroup, ABIBikeInsuranceGroup

If ProductID is 33 I need to look at Vixa column, if it's 31 I need to look at Bike, otherwise I want to look at Axa

This works fine in the chart I am using but if I want to then click on an insurance group in my chart it doesn't allow it because I am passing an if statement,

So to combat this I want to add an additional column in the transactions table - that could be called anything (ABAInsGroup) so that I can use this column in my chart.

Not sure if I am making sense (apologies and thanks for your help).

Re: IF statement in Script

Anonymous — Wed, 13 Jun 2012 09:50:45 GMT

Can you provide us with an example of your QV-document or at least the script you are using?

Re: IF statement in Script

Wed, 13 Jun 2012 10:00:41 GMT

let's try something else:

M_Vixa:

Mapping Load 1, ABIVixaInsuranceGroup Resident ABICode;

M_Bike:

Mapping Load 1, ABIBikeInsuranceGroup Resident ABICode;

M_Axa:

Mapping Load 1, ABIAxaInsuranceGroup Resident ABICode;

Data:

Load ProductID,

if(ProductID = '33', ApplyMap('M_Vixa',1),

if(ProductID = '31', ApplyMap('M_Bike',1),

ApplyMap('M_Axa',1)

)) as NewABIInsuranceGroup

Resident Transactions;