3 Replies Latest reply: Apr 15, 2010 4:03 AM by Steve Fish RSS

    Help with manipulating  data and then saving it in 2 different columns

      Hi, I am new to QlikView and I am in the process of creating my first map and now my first post.

       

      My problem is with coding. What I am trying to achieve is:

      Take my source information (SalesTempOut/CostTempOut) and then split and save it as two new fields (Billed Outgoing Value Gross) and (Billed Outgoing Value Cost Gross). When doing this I need to manipulate the values. For -

      (Billed Outgoing Value Gross)

      If a credit type document i.e the header (Billing Type) = G2 or B3 or Re or S1 multiple by -1 to change the value to a negative value

      Else i.e. all other document types (normal invoices) = should clear the value

      (Billed Outgoing Value Cost Gross)

      If a credit type document i.e the header (Billing Type) = G2 or B3 or Re or S1 leave the value as it is i.e. it will be a positive value

      Else i.e. all other document types (normal invoices) = should clear the value

       

      This would then result in me having two columns for credits only - Negative amounts for the Values and positive amounts for the costs. I am then trying to do the same for invoices, ending up with a total of 4 columns for Invoice (Value and Costs) and Credits (Value and Costs)

       

      Extract of my code below does not work - Please help me with the best practice code.

       

      Regards Steve

       

      [VBRP Bill Doc Item]:

      LOAD

      FKART AS [Billing Type],

      WVWR AS [CostTempIn],

      WAVWR AS [CostTempOut],

      NETWR as [SalesTempIn],

      NETWR as [SalesTempOut],

       

      IF ([Billing Type] = 'G2' OR [Billing Type] = 'B3' OR [Billing Type] = 'RE' OR [Billing Type] = 'S1',

      [SalesTempIn] = 0,

      IF ([Billing Type] <> 'G2' OR [Billing Type] <> 'B3' OR [Billing Type] <> 'RE' OR [Billing Type] <> 'S1',

      [SalesTempIn],

      [SalesTempIn]))

      AS [Billed Incoming Value Gross],

       

      IF ([Billing Type] = 'G2' OR [Billing Type] = 'B3' OR [Billing Type] = 'RE' OR [Billing Type] = 'S1',

      [CostTempIn] = 0,

      IF ([Billing Type] <> 'G2' OR [Billing Type] <> 'B3' OR [Billing Type] <> 'RE' OR [Billing Type] <> 'S1',

      [CostTempIn]*-1,

      [CostTempIn]))

      AS [Billed Incoming Value Cost Gross],

       

      IF ([Billing Type] = 'G2' OR [Billing Type] = 'B3' OR [Billing Type] = 'RE' OR [Billing Type] = 'S1',

      [SalesTempOut]*-1,

      IF ([Billing Type] <> 'G2' OR [Billing Type] <> 'B3' OR [Billing Type] <> 'RE' OR [Billing Type] <> 'S1',

      [SalesTempOut] = 0,

      [SalesTempOut]))

      AS [Billed Outgoing Value Gross],

       

      IF ([Billing Type] = 'G2' OR [Billing Type] = 'B3' OR [Billing Type] = 'RE' OR [Billing Type] = 'S1',

      [CostTempOut],

      IF ([Billing Type] <> 'G2' OR [Billing Type] <> 'B3' OR [Billing Type] <> 'RE' OR [Billing Type] <> 'S1',

      [CostTempOut] = 0,

      [CostTempOut]))

      AS [Billed Outgoing Value Cost Gross]

        • Help with manipulating  data and then saving it in 2 different columns

          I have done the following for anyone who want this. Also as I am new to this if anyone has a more efficient way of getting the result please update.

           

          IF ([Billing Type] = 'G2' OR [Billing Type] = 'B3' OR [Billing Type] = 'RE' OR [Billing Type] = 'S1',

          [SalesTempIn] = ' ',

          [SalesTempIn])

          AS [Billed Incoming Value Gross],

          IF ([Billing Type] = 'G2' OR [Billing Type] = 'B3' OR [Billing Type] = 'RE' OR [Billing Type] = 'S1',

          [CostTempIn] = ' ',

          [CostTempIn]*-1)

          AS [Billed Incoming Value Cost Gross],

          IF ([Billing Type] = 'G2' OR [Billing Type] = 'B3' OR [Billing Type] = 'RE' OR [Billing Type] = 'S1',

          [SalesTempOut]*-1,

          [SalesTempOut] = ' ')

          AS [Billed Outgoing Value Gross],

          IF ([Billing Type] = 'G2' OR [Billing Type] = 'B3' OR [Billing Type] = 'RE' OR [Billing Type] = 'S1',

          [CostTempOut],

          [CostTempOut] = ' ')

          AS [Billed Outgoing Value Cost Gross]

           



            • Help with manipulating  data and then saving it in 2 different columns
              Darius Pranskus

              Hello

              It could be done using following syntax

              IF(INDEX('|G2|B3|RE|S1|', [Billing Type]) > 0, CostTempOut) AS [Billed Outgoing...]

              Function INDEX will search first argument to look if it contains second one, and if it finds, then returns value greater than 0.

              Darius

                • Help with manipulating  data and then saving it in 2 different columns

                  Hi Darius

                   

                  Thanks I appreciate this. I am in the process of replacing my code. One more question please. In the statement you have given me it is looking for document types G2 B3 RE and S1. If I want to turn this code so that it uses all documents that are not G2 B3 RE and S1 - how do I do this? In other words. My old code and the new code you have given me is:

                  Old

                  // IF ([Billing Type] = 'G2' OR [Billing Type] = 'B3' OR [Billing Type] = 'RE' OR [Billing Type] = 'S1',
                  // [CostTempOut],
                  // [CostTempOut] = ' ')
                  // AS [Billed Outgoing Value Cost Gross]


                  New

                  IF(INDEX('|G2|B3|RE|S1|', [Billing Type]) > 0, CostTempOut) AS [Billed Outgoing Value Cost Gross]

                  Now I want to do the same for Billed Incoming Value Cost Gross

                  Old

                  // IF ([Billing Type] = 'G2' OR [Billing Type] = 'B3' OR [Billing Type] = 'RE' OR [Billing Type] = 'S1',
                  // [CostTempIn] = ' ',
                  // [CostTempIn]*-1)
                  // AS [Billed Incoming Value Cost Gross],

                  New

                  ?

                  Many thanks Steve