Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
So my data structure is a bit annoying but what I have is an invoice, invoicedetails and inventory. I am using SQL to pull the information initially so i assume i'll have to do two or more passes on the data, though the more efficient route would be my preferred option.
If it is a custom item, the details of it will be stored in inventory, i was going to do similar to this
if invoicedetails hasn't got the product information then get it from the inventory.
Then the item's proper detail can be added as another table, only the name is kept within invoicedetails.
Example:
InventoryDetails
InvoiceItemKey | ItemKey | ProductDescription |
---|---|---|
JIGS*SALE*I1234*1 | G000AAA | Custom Cable |
JIGS*SALE*I1234*2 | MD233BA | |
JIGS*SALE*I1235*1 | NS-3929BA |
Inventory
ItemKey | ProductDescription |
---|---|
MD233BA | Apple madness pro |
NS-3929BA | Some amazing device |
JOINED InventoryDetails
InvoiceItemKey | ItemKey | ProductDescription |
---|---|---|
JIGS*SALE*I1234*1 | G000AAA | Custom Cable |
JIGS*SALE*I1234*2 | MD233BA | Apple madness pro |
JIGS*SALE*I1235*1 | NS-3929BA | Some amazing device |
Many thanks,
James.
Mapping_Item:
Mapping LOAD // This Should be on the same line
ItemKey,
ItemDescription
Resident Item;
Also I see you've commented this line, just use correct mapping table name it'll work
LOAD Z_ID as InvoiceKey,
Z_ID&'*'&Z_ASSOC_ROW as InvoiceItemKey,
ITEM as ItemKey,
ITEM as ProductCode,
if(len(MISC_DESC)<1,Applymap('Mapping_Item',ITEM,MISC_DESC) , MISC_DESC ) as ProductDescription,
You need ApplyMAP()
Don't join - use Applymap instead
Example
Mapping_Inventory:
Mapping Load
ItemKey
ProductDescription
FROM INVENTORY;
LOAD *,
if(len(ProductDescription)<1,Applymap('Mapping_Inventory',ItemKey,<if not found enter default value here without angle brackets>) , ProductDescription ) as ProductDescription
FROM InventoryDetails
Thank you Vineeth,
And that would place it in InventoryDetails?
few things to note:
Mappingtable should be loaded before being called for in Applymap()
,Applymap('Mapping_Inventory',ItemKey,<if not found enter default value here without angle brackets>)
the second part of Applymap(mappingtable,FieldName,defaultvalue)
Fieldname should exist in the table being loaded and should be the same name as in the table
for Example mapping table fieldname could be different
Example
Mapping_Inventory:
Mapping Load
KEY_for_ITEM
ProductDescription
FROM INVENTORY;
LOAD *,
ItemKey,
if(len(ProductDescription)<1,Applymap('Mapping_Inventory',ItemKey,<if not found enter default value here without angle brackets>) , ProductDescription ) as ProductDescription
FROM InventoryDetails
Thanks for this, but having a few issues, it said it couldn't find it as the table name so i called it the QVD and it then complained about the key
James
Can you post the script?
Sorry i have been massacring your script to try different ways for it to work:
Item:
LOAD
ITEM_CODE as ItemKey,
LONG_DESC as ItemDescription;
SQL SELECT *
FROM "IN_MASTER" WHERE ITEM_CODE LIKE 'MD%';
Mapping_Item:
Mapping
LOAD
ItemKey,
ItemDescription
Resident Item;
Invoice:
LOAD
//Linked keys
"Z_ID" as InvoiceKey,
If(ORD_NO <> '',COMPANY&'*'&WAREHOUSE&'*'&ORD_NO,) as OrderKey,
If(CREDIT_NOTE <> '',COMPANY&'*'&WAREHOUSE&'*'&CREDIT_NOTE,) as CreditKey,
CUSTOMER as CustomerKey,
TRANDATE as TransactionDate,
IN_TRANNO as InventoryTransactionKey,
AR_TRANNO as AccountsReceivableTransactionKey,
//Data
INV_NBR as InvoiceNo,
WAREHOUSE as Warehouse,
Left(INV_NBR,1) as InvoiceType,
CARRIER as CarrierType,
ORDER_TYPE as OrderType,
SALESM_CODE as SalespersonNumber,
OPER as DespatchOperator,
DATE as DespatchDate,
TIME as DespatchTime,
GTOTAL as TotalExVat,
FREIGHT as TotalFreight,
OTH_CHARGES as TotalOtherCharges,
TAX_TOTAL as TotalTax,
INVOICE_TOT as TotalInvoice;
SQL SELECT *
FROM SM_INVOICE WHERE TRANDATE >= '$(vGenDate)' ;
LEFT JOIN (Invoice)
LOAD
Z_ID as CarrierType,
CARR_DESC as CarrierTypeDescription;
SQL SELECT *
FROM "SY_CARRIER";
LEFT JOIN (Invoice)
LOAD
Z_ID as OrderType,
TYPE_DESC as OrderTypeDescription;
SQL SELECT *
FROM "SM_ORDTYPE";
InvoiceDetails:
LOAD Z_ID as InvoiceKey,
Z_ID&'*'&Z_ASSOC_ROW as InvoiceItemKey,
ITEM as ItemKey,
ITEM as ProductCode,
// if(len(MISC_DESC)<1,Applymap('Mapping_Inventory',ITEM,MISC_DESC) , MISC_DESC ) as ProductDescription,
MISC_DESC as ItemInvoiceDescription, // This is for an item which is unique, if null grab data from IN.MASTER? Using ApplyMap? Or left join?
PRICE as UnitPrice,
TAX_RATE as ItemTax,
LINE_REBATE_C as Rebate,
ITEM_LS_DISC as LinePrice, // sometimes this field does not show in STO if the item is 0, check on Qlikview if same anamoly happens, do qty x unitprice
COS_V as Cost1,
COST_VALUE as Cost2,
LINECOST as Cost3;
SQL SELECT SM_INVOICE_ITEM.*
FROM SM_INVOICE_ITEM,SM_INVOICE WHERE SM_INVOICE_ITEM.Z_ID = SM_INVOICE.Z_ID AND SM_INVOICE.TRANDATE >= '$(vGenDate)';
TmpInvoiceDetails:
Load *,
ApplyMap('Mapping_Item', ItemKey,ItemInvoiceDescription) as ProductDescription
Resident InvoiceDetails;
Drop Table TmpInvoiceDetails;
Rename table TmpInvoiceDetails to InvoiceDetails;
InvoiceTracking:
LOAD
Z_ID as InvoiceKey,
TRACKING_NO_C as TrackingNo;
SQL SELECT SM_INVOICE_TRACKING_NO_C.*
FROM SM_INVOICE_TRACKING_NO_C,SM_INVOICE WHERE SM_INVOICE_TRACKING_NO_C.Z_ID = SM_INVOICE.Z_ID AND SM_INVOICE.TRANDATE >= '$(vGenDate)';
InvoiceConsultant:
LOAD
Z_ID as InvoiceKey,
'CONS'&'*'&Z_ID as ConsultantInvoiceKey,
CONSULTANT_C as ConsultantCode;
SQL SELECT SM_INVOICE_CONSULTANT_C.*
FROM SM_INVOICE_CONSULTANT_C,SM_INVOICE WHERE SM_INVOICE_CONSULTANT_C.Z_ID = SM_INVOICE.Z_ID AND SM_INVOICE_CONSULTANT_C.CONSULTANT_C <> '' AND SM_INVOICE.TRANDATE >= '$(vGenDate)';
Mapping_Item:
Mapping LOAD // This Should be on the same line
ItemKey,
ItemDescription
Resident Item;
Also I see you've commented this line, just use correct mapping table name it'll work
LOAD Z_ID as InvoiceKey,
Z_ID&'*'&Z_ASSOC_ROW as InvoiceItemKey,
ITEM as ItemKey,
ITEM as ProductCode,
if(len(MISC_DESC)<1,Applymap('Mapping_Item',ITEM,MISC_DESC) , MISC_DESC ) as ProductDescription,
Thank you very much!
Although, one question, i noticed left joins may be bad, but when I do more than one in my load, it says that it doesn't like the SQL statement afterwards, is there a limit of 1 applymap per table or something?
James