topic Re: count values per customer in App Development

count values per customer

random_user_3869 — Wed, 04 May 2022 10:30:59 GMT

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

Re: count values per customer

brunobertels — Wed, 04 May 2022 11:25:02 GMT

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

Re: count values per customer

random_user_3869 — Wed, 04 May 2022 12:19:46 GMT

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 🙂

Re: count values per customer

random_user_3869 — Wed, 04 May 2022 12:19:55 GMT

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 🙂

Re: count values per customer

random_user_3869 — Wed, 04 May 2022 12:23:51 GMT

my bad i didn't check properly your script.

I'll try and i will let you know

Re: count values per customer

random_user_3869 — Thu, 05 May 2022 13:40:04 GMT

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

Re: count values per customer

random_user_3869 — Sat, 07 May 2022 16:56:33 GMT

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

Re: count values per customer

vinieme12 — Sun, 08 May 2022 18:03:24 GMT

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

Re: count values per customer

random_user_3869 — Wed, 11 May 2022 16:02:49 GMT

thank you it worked