Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Doing calculations between values in different tables in the Script

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

Tags (2)
Community Browser