topic Re: Total qualifier with Only function in QlikView

Total qualifier with Only function

Sun, 03 May 2015 14:34:45 GMT

Hi guys,

Is it possible to use Total qualifier with Only function?

i have a situation where I need to show Dates for my two dimensions.

Something like this...

Table1:

ID LinkedID

1 5

4 7

Table 2:

ID Date

1 01/02/2012

4 03/05/2014

5 08/09/2013

7 08/07/2015

Now the chart should be like this:

ID LinkedID DateforID DateForLinkedID

1 5 01/02/2012 08/09/2013

4 7 03/05/2014 08/07/2015

DateforID can be shown by the simple expression 'Table2:Date.

but DateForLinkedID I need to ignore the ID so i am trying to use only(Total<ID>Date) but it gives me dates for ID and not for LinkedID.

So it looks like this which is not what I want:

ID LinkedID DateforID DateForLinkedID

1 5 01/02/2012 01/02/2012

4 7 03/05/2014 03/05/2014

Any suggestions?

Regards,

Saurabh

Re: Total qualifier with Only function

sunny_talwar — Sun, 03 May 2015 15:04:16 GMT

Is the requirement is to do this on the front end itself? or can we suggest you to do manipulation in the script?

Best,

Sunny

Re: Total qualifier with Only function

Sun, 03 May 2015 15:08:08 GMT

It is on the front end only.

We can not change the script.

I would like to know the both solutions though.

Re: Total qualifier with Only function

sunny_talwar — Sun, 03 May 2015 15:10:50 GMT

For script, you can use mapping load and apply map:

Table1:

LOAD * INLINE [

ID, LinkedID

1, 5

4, 7

];

Table2:

Mapping LOAD * INLINE [

ID, DATE

1, 01/02/2012

4, 03/05/2014

5, 08/09/2013

7, 08/07/2015

];

Table:

LOAD ID,

LinkedID,

ApplyMap('Table2', ID) as DateforID,

ApplyMap('Table2', LinkedID) as DateforLinkedID

Resident Table1;

DROP Table Table1;

Re: Total qualifier with Only function

sunny_talwar — Sun, 03 May 2015 15:11:49 GMT

Output would look something like this in a table box object:

Re: Total qualifier with Only function

sunny_talwar — Sun, 03 May 2015 15:17:24 GMT

For front end use the following expression for DateforLinkedID:

=Pick(Match(ID, 1, 4), Only(TOTAL {<ID = {5}>}Date), Only(TOTAL {<ID = {7}>}Date))

Also find attached the application.

Best,

Sunny

Re: Total qualifier with Only function

Sun, 03 May 2015 15:18:51 GMT

Thanks!

Anyway to do it without changing scripts?

Re: Total qualifier with Only function

Sun, 03 May 2015 15:20:47 GMT

i think you forgot to add the application.

Re: Total qualifier with Only function

sunny_talwar — Sun, 03 May 2015 15:30:59 GMT

Here you go

Re: Total qualifier with Only function

Sun, 03 May 2015 15:38:22 GMT

BTW...i see that you have used hard coded values..

is it possible to not use hard coded values for ID and Linked ID because the data i showed is just an example.

In real, the data could be really big and i may not be able to use had coded value.

Re: Total qualifier with Only function

Sun, 03 May 2015 16:21:21 GMT

Can you please give me little explanation that why is it working the way it is working?

Re: Total qualifier with Only function

Mon, 04 May 2015 19:49:10 GMT

i think i understand how it works.

It has one issue though...

What if there are more than one linked associated with ID?

This Pick match works on the unique value of ID to choose the Only(Total<>) expression but if the iD is not unique then it will not work.

Do you have suggestion for that?

Re: Total qualifier with Only function

sunny_talwar — Mon, 04 May 2015 19:57:39 GMT

I kind of understand what you mean, but will better understand your question if you can provide a sample of what you are trying to say. Can you provide a sample?

Best,

Sunny

Re: Total qualifier with Only function

Mon, 04 May 2015 20:21:01 GMT

I have made slight modifications to the expression and the data to the file you had attached before.

It will help you understand what do I want to say.

Re: Total qualifier with Only function

sunny_talwar — Mon, 04 May 2015 20:34:57 GMT

Slight tweak in the script (not sure if you want to make that also or not), but this might work for you:

Table1:

LOAD *,

ID&LinkedID as Join;

LOAD * INLINE [

ID, LinkedID

1, 5

4, 7

1, 8

];

Table2:

LOAD * INLINE [

ID, Date

1, 01/02/2012

4, 03/05/2014

5, 08/09/2013

7, 08/07/2015

8, 07/06/2010

];

Find attached the application for the front end expressions.

Best,

Sunny

Re: Total qualifier with Only function

Mon, 04 May 2015 20:42:08 GMT

It is a nice trick but my problem is that I cannot change the scripts.

But it gives some idea...

Thanks for that...
BTW..do you think that it is not possible without changing the scripts?

Re: Total qualifier with Only function

sunny_talwar — Mon, 04 May 2015 20:45:02 GMT

From the limited knowledge I have gathered over years, I don't think there is a way, but I may be wrong and someone else might have a better answer for you.

But I am 100% sure that doing it in the script is the best option if you can somehow make that happen.

HTH

Best,

Sunny

Re: Total qualifier with Only function

Mon, 04 May 2015 20:48:23 GMT

Thanks Sunny,

Even though it was not a full solution i learnt few things from you.

Re: Total qualifier with Only function

sunny_talwar — Mon, 04 May 2015 20:49:51 GMT

Yup

We all are learning from each other. I am glad you learnt something from me

Best,

Sunny

Re: Total qualifier with Only function

sunny_talwar — Mon, 04 May 2015 20:54:24 GMT

Hey Saurabh Ramya‌

Check this out (PFA)

May be this is a better option for you.

Best,

Sunny