0 Replies Latest reply: Jun 25, 2015 4:39 AM by Dirk Jacobs RSS

    Doing calculations between values in different tables in the Script

    Dirk Jacobs

      Hi All!

       

      I live in South Africa and I am new to Qlikview and I am learning to master the script language.

       

      My Problem: I have invoice line items and also invoice data in two differing tables connected by an invoice reference number. Some of the invoices are credit notes, however and I would like to transform the values from the invoices to be subtracted in the script (IF-statement) when a field in the invoice line items table indicates that it is a credit note.

       

      The tables look like this:

       

      //Invoice Line Items Table

      [INV LITEMS]:

      load

      if([DOC TYPE] = 'CRN', InvHistLineTotal * -1, InvHistLineTotal) as [LINE TOTAL],  

      if([DOC TYPE] = 'CRN', InvHistLineAmt * -1, InvHistLineAmt) as [LINE AMOUNT],

      if([DOC TYPE] = 'CRN', InvHistLineVatAmt * -1, InvHistLineVatAmt) as [LINE VAT AMOUNT],

                                    //-> I would to do the same calculation for the invoice table below, but [DOC TYPE] is in another table

      *;

        

      SQL SELECT

          InvHistLineAmt,

          InvHistLineDiscount as [LINE DISCOUNT],

          InvHistLineDocLine as [INVOICE LINE NO],

          InvHistLineDocRef as [INVOICE REF],

          InvHistLineDocType as [DOC TYPE],     //-> this is where the INV or CRN indicator is located

          InvHistLinePart as [PART ID],

          InvHistLineQtySuppl as [QTY SUPPLIED],   

          InvHistLineUnitPrice as [UNIT PRICE],

          InvHistLineVatAmt,

          InvHistLineTotal

      FROM InvHistLine;

       

      //Invoice Table

      INVOICES:

      load  

          year(InvHistHdrDT) as YEAR,

        if(month(InvHistHdrDT) < 6

        ,year(InvHistHdrDT)

        ,year(InvHistHdrDT) + 1) as [FIN YEAR],

        if(month(InvHistHdrDT) < 6

        ,month(InvHistHdrDT) + 7

        ,month(InvHistHdrDT) - 5) as [PERIOD],

        dual(month(InvHistHdrDT)&'-'&right(year(InvHistHdrDT),2)

        ,year(InvHistHdrDT)*12+month(InvHistHdrDT)) as YM,

        month(InvHistHdrDT) as MONTH,

        weekname(InvHistHdrDT) as WEEK,

        WeekDay(InvHistHdrDT) as DOW,

        day(InvHistHdrDT) as DAY,    

        mid(date(InvHistHdrDT),6,2) as MOY,

        *;             

          

      SQL SELECT

       

          InvHistHdrAcc as [CUSTOMER ID],

          InvHistHdrAccPer as [ACCOUNT PERIOD],

          InvHistHdrBr as [BRANCH ID],

          InvHistHdrDiscount as [INV DISCOUNT AMT],

          InvHistHdrDriver as DRIVER,

          InvHistHdrDriverBr as [DRIVER BRANCH],

          InvHistHdrDT,

          InvHistHdrFaNo as [FA NO],

          InvHistHdrFleet as [FLEET ID],

          InvHistHdrID as [INVOICE ID],

          InvHistHdrInDT as [IN DATE],

          InvHistHdrInTM as [IN TIME],

          InvHistHdrKmIn as [KM IN],

          InvHistHdrKmOut as [KM OUT],

          InvHistHdrKms as [KMS],

          InvHistHdrKmsIn as [KMS IN],

          InvHistHdrLines as [LINE NO],

          InvHistHdrNetTot,

          InvHistHdrNoDays as [NO OF DAYS],

          InvHistHdrOrder as [ORDER NO],

          InvHistHdrOutDT as [OUT DATE], 

          InvHistHdrOutTM as [OUT TIME],

          InvHistHdrRANo as [RANO],  

          InvHistHdrRefDoc as [INVOICE REF],

          InvHistHdrRefDT as [REF DATE],

          InvHistHdrRegNo as [REGISTRATION NO],

          InvHistHdrSubhBr as [SUBH BRANCH],  

          InvHistHdrTime as [TIME],   

          InvHistHdrValue as [ORDER VALUE AMT],

          InvHistHdrVehSubh as [VEHICLE SUBH],

          InvHistHdrVatAmt as [ORDER VAT]

      FROM InvHistHdr AS I

      where year(I.InvHistHdrDT) = 2004 OR year(I.InvHistHdrDT) = 2005 OR year(I.InvHistHdrDT) = 2006;

       

      I was thinking of following this strategy:

       

      1. Join INVOICES and INV LITEMS and call it: [INV TEMP]

      2. LOAD the new table as a resident load and do the required calculation

      3. Drop the joined table

       

      Any advice from the good folk in the QV community?

       

      Regards in advance