Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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.
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)
Naming convention is ok. I typed it on the text which might requires typo
Can you post sample data.? It would be helpful to give you the script.
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);
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
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.