How to crate a field based on multiple dimension values

[vishalmanu]

Hello Experts,

I cam across a scenario where a new field should be created in a table with the help of the other two dimension values that are available in the same table.

please find below the attached data.

task	Sales Man ID	store person Name	Type	Store	contacted store
Sales	1	A			Y
Sales	2	A			Y
Sales	3	B			N
Sales	1	B			N
Sales	3	C			Y
Sales	5				N
Sales	4	C			Y
Sales	3	C			N

n the above table we have the sales man Id and store person
The contacted store field shows if the sales Man and store person had reached the sales target ('Y' means yes and 'N' means No)

I want the output as explained below 

task	Sales Man ID	Sales Rep Name	Type	Store	contacted store	Output	O/P commenst
Sales	1	A			Y	Y	since the value of field Contacted store is Y
Sales	2	A			Y	Y	since the value of field Contacted store is Y
Sales	3	B			N	N	since the value of field Contacted store is  N
Sales	1	B			N	Y	Even though the field Contacted store is N, the sales man has already contacted one of the store Iin the above row no.2, so Yes
Sales	3	C			Y	Y	since the value of field Contacted store is Y
Sales	5				N	N	since the value of field Contacted store is  N
Sales	4	C			Y	Y	since the value of field Contacted store is Y
Sales	3	C			N	Y	Even though the field Contacted store is N, the sales man has already contacted one of the store Iin the above row no.6, so Yes

And i would also need the count of Yes and No of the above output to be shown in the a pie chart.

Kindly help me with this scenario, as i tried to work with AGGR() but could not get it done.

Thanks in advance for your help.

Best Regards,

Vishnu T

[Taoufiq_Zarra]

Hi,

One solution :

Data:

LOAD * INLINE [
    task, Sales Man ID, store person Name, Type, Store, contacted store
    Sales, 1, A,  ,  , Y
    Sales, 2, A,  ,  , Y
    Sales, 3, B,  ,  , N
    Sales, 1, B,  ,  , N
    Sales, 3, C,  ,  , Y
    Sales, 5,  ,  ,  , N
    Sales, 4, C,  ,  , Y
    Sales, 3, C,  ,  , N
];



left join

load rowno() as IDtmp,concat([contacted store],'') as Tmp,[Sales Man ID] resident Data group by [Sales Man ID];

output:
noconcatenate

load *, if([contacted store]='Y','Y',if(index(left(Tmp,ID2),'Y')>0,'Y','N')) as output;
load *,if([Sales Man ID]=previous([Sales Man ID]),peek('ID2')+1,1) as ID2 resident Data;

drop table Data;
drop fields ID2,IDtmp,Tmp;

output:

Pie chart :

Dimension :

[output]

Measure :

count([store person Name])

[vishalmanu · Accepted solution]

Hello Taufiq,

Thanks for the solution provided .. Is there any possibility to do it in the front end rather than in the script?

Thanks in advance for your help & answer.

Best Regards,

Vishal