7 Replies Latest reply: Jan 7, 2016 12:05 AM by Shawn Ho RSS

    How do i have more fields in NoConcat

    ben lim

      Hi I have a table with orders particulars and would like to create a CRM table out of this by checking whether is the customers that booked are repetitive customers.

      Orders:

      LOAD

      Order_ID,

      Order_Type,

      Phone_No,

      Customers_ID,

      FROM

      xxx

      (ooxml, embedded labels, table is Orders);

       

      CRM:

      NoConcatenate

      Customers_Status:

      Load

      //AutoNumber(rowno()) as Customers_ID,

      Phone_No,

      if(Count(DISTINCT(Customers_ID))>1,'Existing','New') as Cust_Status

      Resident Orders

      WHERE match([Order type],'New','Amendment')>0

      Group by Phone_No;

       

      on the CRM, I would like to have more fields such as Guest First Name, Guest Last Name that I can get it from Orders too but would like to have a complete set of customers data in the NoConcat CRM table. Also I would like to create a customized field on the CRM that shows me Min(Order_Date) so that I can check when did the customers first purchase our product.

       

      Can these all be done as I keep receieving errors from QV.

        • Re: How do i have more fields in NoConcat
          Tamil Nagaraj

          Hi Ben Lim,

           

          Try,

          Orders:
          LOAD
          Order_ID,
          Order_Type,
          Phone_No,
          Customers_ID
          FROM
          XXXXX
          (ooxml, embedded labels, table is Orders);
          
          NoConcatenate
          CRM:
          Load
          //AutoNumber(rowno()) as Customers_ID,
          Phone_No,
          if(Count(DISTINCT(Customers_ID))>1,'Existing','New') as Cust_Status
          Resident Orders
          WHERE match([Order_Type],'New','Amendment')
          Group by Phone_No;
          
          
          

           

          I could see comma at the end of Customers_ID field. It should be removed. Also, you mentioned wrong name in match function.i.e Order type should be Order_Type.


          Let me know.

          • Re: How do i have more fields in NoConcat
            Jonathan Dienst

            I think you misunderstand what NoConcatenate means - it means to override the automatic concatenation of tables that have the same set of fields (like UNION ALL in SQL).

             

            I suspect that you need a mapping table to apply the status - something like:

             

            MapStatus:

            Load

            Customers_ID,

            if(Count(DISTINCT(Customers_ID))>1,'Existing','New') as Cust_Status

            Resident Orders

            WHERE Match([Order type], 'New', 'Amendment');

             

            Orders:

            LOAD

            Order_ID,

            Order_Type,

            Phone_No,

            Customers_ID,

            ApplyMap('MapStatus', Customers_ID, 'None') As Status

            FROM xxx

            (ooxml, embedded labels, table is Orders);

              • Re: How do i have more fields in NoConcat
                Jonathan Dienst

                If OrderDate exists in Orders, then you could create a second mapping table like:

                 

                MapFirstOrder:

                Load

                Customers_ID,

                Min(OrderDate) As FirstOrder

                Resident Orders

                Group By Customers_ID;

                 

                And add the second ApplyMap to the main load:

                 

                ApplyMap('MapStatus', Customers_ID, 'None') As Status,

                ApplyMap('MapFirstOrder', Customers_ID, Null()) As FirstOrder

              • Re: How do i have more fields in NoConcat
                Shawn Ho

                This bit looks wrong:

                 

                CRM:

                NoConcatenate

                Customers_Status:

                call it CRM or Customers_Status, but not both.

                 

                This is what I would suggest (keeping in mind that the code below is not tested):

                 

                1. Load Orders table (as you have)

                2.

                     Left Join (Orders) Load

                          Customers_ID,

                          Count(distinct Order_ID) as No_Of_Orders,

                          Min(Order_Date) as Min_Order_Date,

                          Max(Order_Date) as Max_Order_Date

                     Resident Orders

                     Group By Customers_ID;


                3. Load CRM table (as a dimensional table)

                 

                S.