How do i have more fields in NoConcat

[n1ef5ng1]

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.

[tamilarasu]

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.

[n1ef5ng1]

Hi

This two table will then join with unique key phone number. However I need to do cohort analysis... which requires me to check min(date) for each customers that they joined. i cant do that on the Order table as the table are based on per order entry (one cust might have > 1 entry on order table)

[n1ef5ng1]

Naming convention is ok. I typed it on the text which might requires typo

[tamilarasu]

Can you post sample data.?  It would be helpful to give you the script.

[jonathandienst]

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

[jonathandienst]

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

[shawn-qv · Accepted solution]

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.