10 Replies Latest reply: Jul 20, 2015 4:30 AM by David Gilligan RSS

    Possible with IF?

      Hello again,

       

      I have the following IF, which works perfectly:

       

      Load *,
      If(Flag1) = 'S', tempSpend_NOK * -1, tempSpend_NOK) As ActualSpend_NOK,
      If(Flag1 = 'S', tempTax_NOK * -1, tempTax_NOK) As ActualTax_NOK,
      If(Flag1 = 'S', tempNetSpend_NOK * -1, tempNetSpend_NOK) As ActualNetSpend_NOK

      Resident Table1;

       

      Basically Flag1 determines if the signs should be inverted or not (*-1)....takes the gross, taxesm subtracts for net Norwegian Crowns

      Bit I want to take things further, if possible.  I want to use a different field value based on different criteria.  Here is a non-working example:

       

      Load *,

      If(Flag1) = 1, TheField, TheField [WHERE Flag1 = 1) As WhatIsFlag1,

      If(Flag2 = 1, TheField2, TheField2 * AnotherField [WHERE TheYear = 2015) As InverIfNeeded

      Resident Table1;

       

      Some of these might get quite involved so I wonder if I am even on the right track for this?  Is there a way to handle it in an IF...CASE....do it in SQL instead?

       

      Thanks in advance for any advice.

       

       

        • Re: Possible with IF?
          Sinan Ozdemir

          Hi David,

           

          I would just handle this in another preceding load statement:

           

          Load *

          Where Flag1 = 1 And TheYear = 2015;

          Load *,

          If(Flag1) = 1, TheField, TheField) As WhatIsFlag1,

          If(Flag2 = 1, TheField2, TheField2 * AnotherField) As InverIfNeeded

          Resident Table1;


          • Re: Possible with IF?
            Antonio Mancini

            If(Flag1 = 'S',-1,1)*tempSpend_NOK As ActualSpend_NOK

            • Re: Possible with IF?

              Hello again...OK...let me try with another example....a real one that I was hoping to do with an IF....or in the LOAD....(sorry for the bad examples before)...

               

              Let's say I have 3 fields, a key (TheKey), A Supplier ID (Supplier) and Line Number (LineNo)....

               

              What I would like to do is, if the Supplier is = to "" (blank but not null), then I want to take the Supplier from LineNo = 1 WHERE for the same key.  One key = 1 line on the invoice, so there can be 10 lines, 9 blank (or maybe less but always there will be one on line number 1)

               

              I tried as an IF and in the LOAD but always come up with a syntax error that doesn't really reflect the problem (it will appear later in the script and have nothing to do with anything I did).

               

              Thanks again!

                • Re: Possible with IF?

                  OH....and also, I would need to create this as a new field, say Supplier2...

                    • Re: Possible with IF?
                      Sinan Ozdemir

                      Hi David,

                       

                      It might be better if you post a sample dataset which can reflect the complexity. Sometimes it is difficult to determine the code without seeing the dataset.

                       

                      Thanks

                        • Re: Possible with IF?

                          I can do that....in the mean time, mayhbe this will help.  What I really want to learn how to do, if I can do it, is make in QlikView, basically an SQL Subselect statement.  So, very simply (the dataset is hundreds of thousands of rows):

                           

                          LOAD
                          *,
                          TheKey
                          Supplier,

                          RowNo,,
                          if(RowNo = 1, Supplier, (SELECT Supplier WHERE RowNo=1) AS Supplier2,   //Sort of how I would do it in SQL
                          ;

                           

                          So, for example:

                          TheKey     Row No.     Supplier    

                          111              1                    Acme

                          112              2

                          113          3

                          ...

                          201          1               Apple

                          202          2              

                          203          3               Apple    

                          204          4

                           

                          Would become:

                          So, for example:

                          TheKey     Row No.     Supplier    

                          111              1                    Acme

                          112              2                    Acme

                          113          3               Acme

                          ...

                          201          1               Apple

                          202          2               Apple

                          203          3               Apple    

                          204          4               Apple

                    • Re: Possible with IF?
                      Settu Periyasamy

                      Hi David,

                      you can use the Peek() function to get the previous value.

                      like this..

                      Check:
                      Load * INLINE [
                           TheKey,RowNo,Supplier
                          111, 1,Acme
                          112, 2,
                          113, 3,
                          201, 1,Apple
                          202, 2,
                          203, 3,Apple
                          204, 4,
                      ];
                      Final:
                      NoConcatenate
                      Load TheKey,
                        RowNo,
                        if(Len(Trim(Supplier))=0 or IsNull(Supplier),Peek(Supplier2),Supplier) as Supplier2
                        Resident Check
                      Order By TheKey, RowNo asc;
                      
                      
                      Drop Table Check;
                      

                       

                      71.JPG

                      PFA for reference.