Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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])
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