Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello everyone,
i have the following table
LOAD * Inline [
CustomerID , orderid , salechannel , dateofsale (DD/MM/YYYY)
123 , abc1 , WEB, 01/01/2022
145 , abd1 , RETAIL, 04/05/2022
123 , abc2 , APP, 01/01/2022
145 , abd1 , WEB, 04/05/2022
];
I would like to calculate the Percentage of customers who ordered through more than 2 sales channels over from january 2022 to june 2022
For customer '123' he ordered through two differents channel over this period/
I would like a table like this in the end
FINAL TABLE //from january to june 2022
LOAD * Inline [
CustomerID , Number of order more than two channel ,
123 , 2
145 , 2
];
do you know how to do this ?
Thank you in advance
FACT:
Load
[CODE CLIENT DTM],
[Libellé canal de vente]
FROM [$(vs_QVD)/$(vs_QVD_Prefixe_Niv2)_CA_CANL_CLNT_PAR_PERD_TEST.qvd]
(qvd);
Multichannel:
Load *
Where ChannelCount>1
;
Load
[CODE CLIENT DTM]
,Count(Distinct [Libellé canal de vente]) as ChannelCount
Resident FACT
Group by [CODE CLIENT DTM];
hi
may be this
temp:
LOAD * Inline [
CustomerID , orderid , salechannel , dateofsale (DD/MM/YYYY)
123 , abc1 , WEB, 01/01/2022
145 , abd1 , RETAIL, 04/05/2022
123 , abc2 , APP, 01/01/2022
145 , abd1 , WEB, 04/05/2022
];
final:
load
CustomerID,
count( orderid) as Nbr_of_order,
if(count(orderid) > 1,1,0) as Flag //
resident temp
group by CustomerID,orderid ;
NoConcatenate
load *
where Flag>1;
drop table temp
thanks for the information.
But basically what i want is to count for each customer ID, the number of DISTINCT salechannel.
if i analyze this
LOAD * Inline [
CustomerID , orderid , salechannel , dateofsale (DD/MM/YYYY)
123 , abc1 , WEB, 01/01/2022
145 , abd1 , RETAIL, 04/05/2022
123 , abc2 , APP, 01/01/2022
145 , abd1 , WEB, 04/05/2022
];
basically for customerid 123 he has two distinct salechannel which is WEB and APP.
Basically i want to count the number of distinct values inside the salechannel field for each custoùer.
Thank you 🙂
thanks for the information.
But basically what i want is to count for each customer ID, the number of DISTINCT salechannel.
if i analyze this
LOAD * Inline [
CustomerID , orderid , salechannel , dateofsale (DD/MM/YYYY)
123 , abc1 , WEB, 01/01/2022
145 , abd1 , RETAIL, 04/05/2022
123 , abc2 , APP, 01/01/2022
145 , abd1 , WEB, 04/05/2022
];
basically for customerid 123 he has two distinct salechannel which is WEB and APP.
Basically i want to count the number of distinct values inside the salechannel field for each custoùer.
Thank you 🙂
my bad i didn't check properly your script.
I'll try and i will let you know
Hello
when i do that
TEMP:
Load
[CODE CLIENT DTM],
[Libellé canal de vente]
FROM [$(vs_QVD)/$(vs_QVD_Prefixe_Niv2)_CA_CANL_CLNT_PAR_PERD_TEST.qvd]
(qvd);
TEMP_2:
Load
[CODE CLIENT DTM],
[Libellé canal de vente],
count(DISTINCT( [Libellé canal de vente])) AS [flag],
// If(count(DISTINCT( [Libellé canal de vente])) >= 2,1,0) as [Flag multicanaux],
count([CODE CLIENT DTM]) AS [NOMBRE CLIENT]
Resident TEMP
Group By
[CODE CLIENT DTM],
[Libellé canal de vente];
Drop Table TEMP;
TEMP_3:
NoConcatenate
load *
Resident TEMP_2
where [flag] > 1;
drop table TEMP_2;
i get zero value (0 row)
can you help me
I still get zero values
from that script
TEMP:
Load
[CODE CLIENT DTM],
[Libellé canal de vente]
FROM [$(vs_QVD)/$(vs_QVD_Prefixe_Niv2)_CA_CANL_CLNT_PAR_PERD_TEST.qvd]
(qvd);
TEMP_2:
Load
[CODE CLIENT DTM],
// [Libellé canal de vente],
count(DISTINCT( [Libellé canal de vente])) AS [Nombre distinct de canal],
if(count(distinct([Libellé canal de vente])) > 1,1,0) as Flag
Resident TEMP
Group By
[CODE CLIENT DTM],
[Libellé canal de vente];
Drop Table TEMP;
TEMP_3:
Noconcatenate Load
*
Resident TEMP_2
Where Flag >1;
Drop Table TEMP_2;
It's like the flasg "if(count(distinct([Libellé canal de vente])) > 1,1,0) as Flag" doesn't work.
Anyone can help ?
Thank you
FACT:
Load
[CODE CLIENT DTM],
[Libellé canal de vente]
FROM [$(vs_QVD)/$(vs_QVD_Prefixe_Niv2)_CA_CANL_CLNT_PAR_PERD_TEST.qvd]
(qvd);
Multichannel:
Load *
Where ChannelCount>1
;
Load
[CODE CLIENT DTM]
,Count(Distinct [Libellé canal de vente]) as ChannelCount
Resident FACT
Group by [CODE CLIENT DTM];
thank you it worked