topic Pivot customer id counts in QlikView

Pivot customer id counts

bhuprakash — Wed, 25 Nov 2020 16:16:04 GMT

Dear All,

I am not able to get sales territory code wise customer id count in the second table.

In the second table, i want the data like below -

Dist_TSH | STC | StCode | StateName | Count of Customer ID

Dist_TSH -> Name of Active SIO

STC -> Sales territory code in which active SIO working

STCode -> State code in which active SIO working

StateName -> Name of State in which active SIO working

Please help me to get this data so that after this I can add this data in my first table.

Re: Pivot customer id counts

Anil_Babu_Samineni — Sun, 20 May 2018 13:02:17 GMT

Is this you are expecting?

=Dist_TSH & '|' & STC & '|' & StCode & '|' & StateName

Re: Pivot customer id counts

bhuprakash — Sun, 20 May 2018 14:49:57 GMT

Dear Anil,

Below is the required result-

Dist_TSH	STC	StCode	StateName	Distributor Counts
ABHINANDAN KUMAR	BR-T11	BR	Bihar	36
Anupam Das	WB-1-T18	WB	West Bengal	7
BALJINDER SINGH	HR-2-T3	HR	Haryana	36
Bapan Ghosh	WB-1-T20	WB	West Bengal	10
Biplab Manik	WB-1-T12	WB	West Bengal	7
D KISHORE	TNK-6-T2	TN	Tamil Nadu	3
DEEPAK BRAHMA NAND SHARMA	MP-3-T1	MP	Madhya Pradesh	34
GURPREET SINGH	PB-3-T3	PB	Punjab	27
HEMANT VITTHAL MESHRAM	E-MH-5-T3	MH	Maharashtra	7
JASPREET SINGH	PB-5-T2	PB	Punjab	82
KAMALDEEP SINGH	PB-3-T4	PB	Punjab	94
KULDIPKUMAR PATEL	GJ-1-T17	GJ	Gujarat	29
MANISH PRAJAPATI	GJ-1-T1	GJ	Gujarat	26
Maruri Rakashekar Reddy	AP-1-T9	AP	Andhra Pradesh	36
NIKESH PRAKASHRAO GODBOLE	E-MH-1-T3	MH	Maharashtra	57
NITESH KUMAR	UP-2-T7	UP	Uttar Pradesh	22
PANKAJ KUMAR SHARMA	RJ-T4	RJ	Rajasthan	67
PARAMPAL SINGH	PB-5-T1	PB	Punjab	43
PAWAN KUMAR	PB-5-T3	PB	Punjab	72
PRAFULLA RAJENDRARAO SONTAKKE	E-MH-6-T2	MH	Maharashtra	21
Proddatur - No Territory Head	S-AP-2-T3	AP	Andhra Pradesh	1
RAGHUVEER SINGH	RJ-T6	RJ	Rajasthan	37
SACHIN JANARAOJI TEWARE	E-MH-1-T1	MH	Maharashtra	32
SANJAY MADHUKAR SONAWANE	W-MH-3-T3	MH	Maharashtra	12
SANJEET KUMAR	BR-T6	BR	Bihar	48
SHARAD SUBHASRAO PAWALE	MH-1-T9	MH	Maharashtra	8
VIKRAMSINH SISODIYA	GJ-1-T6	GJ	Gujarat	50

Re: Pivot customer id counts

parthesh — Sun, 20 May 2018 14:56:42 GMT

Hello,

i have tried to create new table, Please find attached QVW file.

hope that you were looking for same.

let me know in case of you are looking something else.

if your query resolved please dont forget to mark this as correct answer.

Re: Pivot customer id counts

bhuprakash — Sun, 20 May 2018 14:57:37 GMT

Kindly search the STC code in customer master table. There is a column [Territory Code] kindly search STC code in the column for each employee and get total count of [customer no]

Re: Pivot customer id counts

bhuprakash — Sun, 20 May 2018 15:01:56 GMT

Dear Parth,

I dont see the qvw file. how to get it ?

Re: Pivot customer id counts

parthesh — Sun, 20 May 2018 15:06:02 GMT

i have attached it in my reply, i am able to see it may be it will reflect after one or two minute. i have double checked it is there. 🙂

Re: Pivot customer id counts

bhuprakash — Sun, 20 May 2018 15:12:01 GMT

Parthesh,

For example, Abhinandan is mapped with 36 distributors. I tell you that how can you find it.

I have mapped territory code (STC) against all employees as you can see above. to get distributor count, just see the distributor code column in customer master table and filter with STC code then you can find distributor count.

Re: Pivot customer id counts

parthesh — Sun, 20 May 2018 15:33:17 GMT

Hello, here i have tried by selecting Abhinandan, and i can see 137 customer attached with him. i dont know how you are getting 36. are you trying with same QVW, same data?

Re: Pivot customer id counts

bhuprakash — Sun, 20 May 2018 15:45:35 GMT

Dear Parth,

I have attached file again in starting of the conversation and tried to explain you that how 36 count is coming.

Re: Pivot customer id counts

bhuprakash — Sun, 20 May 2018 15:48:00 GMT

If you check last column in Abhi file then 5 territory codes are coming but Abhinandan is mapped only with one territory

BR-T11.