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
