topic Re: Merging data in two data fields with SQL in QlikView

Merging data in two data fields with SQL

james_hanify — Wed, 07 Dec 2016 15:00:47 GMT

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
JIGSSALEI1234*1	G000AAA	Custom Cable
JIGSSALEI1234*2	MD233BA
JIGSSALEI1235*1	NS-3929BA

Inventory

ItemKey	ProductDescription
MD233BA	Apple madness pro
NS-3929BA	Some amazing device

JOINED InventoryDetails

InvoiceItemKey	ItemKey	ProductDescription
JIGSSALEI1234*1	G000AAA	Custom Cable
JIGSSALEI1234*2	MD233BA	Apple madness pro
JIGSSALEI1235*1	NS-3929BA	Some amazing device

Many thanks,

James.

Re: Merging data in two data fields with SQL

vinieme12 — Wed, 07 Dec 2016 15:07:52 GMT

You need ApplyMAP()

Don't join - use Applymap instead

Re: Merging data in two data fields with SQL

vinieme12 — Wed, 07 Dec 2016 15:12:00 GMT

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

Re: Merging data in two data fields with SQL

james_hanify — Wed, 07 Dec 2016 15:18:50 GMT

Thank you Vineeth,

And that would place it in InventoryDetails?

Re: Merging data in two data fields with SQL

vinieme12 — Wed, 07 Dec 2016 15:23:02 GMT

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

Re: Merging data in two data fields with SQL

james_hanify — Wed, 07 Dec 2016 16:18:08 GMT

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

Re: Merging data in two data fields with SQL

vinieme12 — Wed, 07 Dec 2016 16:50:35 GMT

Can you post the script?

Re: Merging data in two data fields with SQL

james_hanify — Wed, 07 Dec 2016 17:04:42 GMT

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

Re: Merging data in two data fields with SQL

vinieme12 — Wed, 07 Dec 2016 18:01:10 GMT

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,

Re: Merging data in two data fields with SQL

james_hanify — Thu, 08 Dec 2016 11:07:37 GMT

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

Re: Merging data in two data fields with SQL

vinieme12 — Thu, 08 Dec 2016 11:10:05 GMT

No, there is no limit .

Do you get any error message?

Re: Merging data in two data fields with SQL

james_hanify — Thu, 08 Dec 2016 11:19:44 GMT

School boy error, i copied and pasted and forgot to change the as name

Thanks