Total qualifier with Only function

[#1]

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

[sunny_talwar]

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

[#3]

It is on the front end only.

We can not change the script.

I would like to know the both solutions though.

[sunny_talwar]

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;

[sunny_talwar]

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

[sunny_talwar]

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

[#7]

Thanks!

Anyway to do it without changing scripts?

[#8]

i think you forgot to add the application.

[sunny_talwar]

Here you go

[#10]

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.