11 Replies Latest reply: Dec 8, 2016 6:19 AM by James Hanify RSS

    Merging data in two data fields with SQL

    James Hanify

      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.

        • Re: Merging data in two data fields with SQL
          Vineeth Pujari

          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

              Thank you Vineeth,

              And that would place it in InventoryDetails?

                • Re: Merging data in two data fields with SQL
                  Vineeth Pujari

                  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

                      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
                          Vineeth Pujari

                          Can you post the script?

                            • Re: Merging data in two data fields with SQL
                              James Hanify

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