13 Replies Latest reply: Jun 13, 2012 6:00 AM by Enrique Bernat RSS

    IF statement in Script

      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
          Enrique Bernat

          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

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

               

              Kev.

                • Re: IF statement in Script
                  Enrique Bernat

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

                    • Re: IF statement in Script

                      These are tables created in Qlikview not SQL.

                      • Re: IF statement in Script
                        Enrique Bernat

                        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

                            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
                                Dennis Hoogenboom

                                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

                                    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
                                        Enrique Bernat

                                        ¿The primary key of ABICode table is ProductID?

                                        • Re: IF statement in Script
                                          Dennis Hoogenboom

                                          Mabye a typo?

                                           

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

                                            • Re: IF statement in Script

                                              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
                                                  Dennis Hoogenboom

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

                                                  • Re: IF statement in Script
                                                    Enrique Bernat

                                                    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;