count values per customer

[random_user_3869]

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 

[brunobertels]

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

[random_user_3869]

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 🙂

[random_user_3869]

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 🙂

[random_user_3869]

my bad i didn't check properly your script.

I'll try and i will let you know

[random_user_3869]

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

[random_user_3869]

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

[vinieme12 · Accepted solution]

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

[random_user_3869]

thank you it worked