Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
james_hanify
Creator
Creator

Merging data in two data fields with SQL

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

InvoiceItemKeyItemKeyProductDescription
JIGS*SALE*I1234*1G000AAACustom Cable
JIGS*SALE*I1234*2MD233BA
JIGS*SALE*I1235*1NS-3929BA


Inventory

ItemKeyProductDescription
MD233BAApple madness pro
NS-3929BASome amazing device

JOINED InventoryDetails

InvoiceItemKeyItemKeyProductDescription
JIGS*SALE*I1234*1G000AAACustom Cable
JIGS*SALE*I1234*2MD233BAApple madness pro
JIGS*SALE*I1235*1NS-3929BASome amazing device

Many thanks,

James.

1 Solution

Accepted Solutions
vinieme12
Champion III
Champion III


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,

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.

View solution in original post

11 Replies
vinieme12
Champion III
Champion III

You need ApplyMAP()

Don't join - use Applymap instead

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
vinieme12
Champion III
Champion III

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

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
james_hanify
Creator
Creator
Author

Thank you Vineeth,

And that would place it in InventoryDetails?

vinieme12
Champion III
Champion III

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

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
james_hanify
Creator
Creator
Author

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

vinieme12
Champion III
Champion III

Can you post the script?

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
james_hanify
Creator
Creator
Author

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)';

vinieme12
Champion III
Champion III


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,

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
james_hanify
Creator
Creator
Author

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