topic Re: Expression problem in QlikView

Expression problem

Wed, 25 Nov 2020 16:16:04 GMT

Hi all,

I have got an Excel Pivot table like the one below,

I have tried this in QV ,please the attached files

Dimension:=ValueList('C2D_Used','No_Of_Agents') and Day

Expresion:

=pick(Match(ValueList('C2D_Used','No_Of_Agents'),

'C2D_Used','No_Of_Agents'),

if(sum(if(CallTime > 0 , 1, 0)) > 0,'Y','N'),

Count(DISTINCT Consultant))

This does not give the needed result, it does not give me the 'N'. please assist.

PFA

Re: Expression problem

Anil_Babu_Samineni — Thu, 08 Dec 2016 02:15:02 GMT

I didn't find Cash Investment at all on your Excel. Where do you pick that?

Re: Expression problem

sunny_talwar — Thu, 08 Dec 2016 03:20:17 GMT

This does not give the needed result, it does not give me the 'N'.

What is the needed result? Can you share what you expect to see?

Re: Expression problem

Thu, 08 Dec 2016 06:40:11 GMT

Hi Sunny T ,

I actually want to convert the two expressions into dimensions, just as they are shown in the pivot.

Regards

Re: Expression problem

Thu, 08 Dec 2016 06:40:47 GMT

I actually want to convert the two expressions into dimensions, just as they are shown in the pivot.

Re: Expression problem

pathiqvd — Thu, 08 Dec 2016 07:14:14 GMT

Hi,

You want like this.

Re: Expression problem

Thu, 08 Dec 2016 07:28:47 GMT

This exactly what I am looking for and the date should be per day.

Re: Expression problem

pathiqvd — Thu, 08 Dec 2016 07:40:12 GMT

Hi,

you want like this,

Re: Expression problem

Thu, 08 Dec 2016 07:58:47 GMT

Exactly that

Re: Expression problem

pathiqvd — Thu, 08 Dec 2016 08:08:00 GMT

Hi,

K. Script level write like this:-

Test:

LOAD CC,

Desk,

Consultant,

Team_Leader,

Year,

Month,

Day,

CallTime,

interval(CallTime,'ss') as CallTime1

FROM

C:\Users\arul.settu\Desktop\Test_data.xlsx

(ooxml, embedded labels, table is Sheet1);

And Chart level

Dim:-

1)=ValueList('C2D_Used','No_Of_Agents')

2)=ID

Exp:-

=if(ValueList('C2D_Used','No_Of_Agents')='C2D_Used',if(count({<CallTime1={'0','00'}>}CallTime1)> 0,'Yes','No'),

if(ValueList('C2D_Used','No_Of_Agents')='No_Of_Agents',Count(DISTINCT Consultant)))

Re: Expression problem

Thu, 08 Dec 2016 08:51:35 GMT

Thank you so much, it worked.