Skip to main content
Announcements
Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in. SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
vishalmanu
Partner - Creator
Partner - Creator

How to crate a field based on multiple dimension values

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.

 

taskSales Man IDstore person NameTypeStorecontacted store
Sales1A  Y
Sales2A  Y
Sales3B  N
Sales1B  N
Sales3C  Y
Sales5   N
Sales4C  Y
Sales3C  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 

 

taskSales Man IDSales Rep NameTypeStorecontacted storeOutputO/P commenst
Sales1A  YYsince the value of field Contacted store is Y
Sales2A  YYsince the value of field Contacted store is Y
Sales3B  NNsince the value of field Contacted store is  N
Sales1B  NYEven 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
Sales3C  YYsince the value of field Contacted store is Y
Sales5   NNsince the value of field Contacted store is  N
Sales4C  YYsince the value of field Contacted store is Y
Sales3C  NYEven 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

Labels (4)
1 Solution

Accepted Solutions
vishalmanu
Partner - Creator
Partner - Creator
Author

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

View solution in original post

2 Replies
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:

Capture.PNG

Pie chart :

Dimension :

[output]

Measure :

count([store person Name])

Capture1.PNG

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
vishalmanu
Partner - Creator
Partner - Creator
Author

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