Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Total Modifier Question

Hi guys,

Here is my data set.

Table1:

ID  LinkedID

1    5

4    7

1     8

Table 2:

ID    Date

1    01/02/2012

4    03/05/2014

5    08/09/2013

7    08/07/2015

8     05/04/2014

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

1       8                  01/02/2012   05/04/2014

The dimension used is

ID&LinkedID

ID

LinkedID

and expression used is

1. only(Date)

2. Pick(Match(ID&LinkedID, $(=concat(ID&LinkedID, ','))), Only(TOTAL {<ID = {5}>} Date),  Only(TOTAL {<ID = {8}>} Date), Only(TOTAL {<ID = {7}>}Date))

It gives the right chart but I cannot use the hard coded value in only(Total<>) expression because in real world there would be plenty of unknown values of linkedID . it has to be replaced by something dynamic which can produce the right value of ID for each row.

Any suggestion?

Thanks,

Saurabh

15 Replies
ramoncova06
Specialist III
Specialist III

in your total add the the field grouping <ID>

is like this

sum(total <field> metric)

Not applicable
Author

Sorry but i did not understand.

Can you please give me the full expression?

ramoncova06
Specialist III
Specialist III

This will do the grouping by ID, for your total

Pick(Match(ID&LinkedID, $(=concat(ID&LinkedID, ','))), Only(TOTAL <ID> Date))

Not applicable
Author

Screen Shot 2015-05-04 at 2.59.32 PM.png

Hi Ramon,

It is not working...

it is giving me the above output...check the last column.

The last column should have the same value as Column DateForLinkedID.

Saurabh

ramoncova06
Specialist III
Specialist III

yes I just realized this myself

may I ask why didn't you continue with the mapping that you had in your the file? That would be several times better than doing this with your complex expression in the front end

sunny_talwar

I am back ... See if you like the method I have used in the attached file.

Best,

Sunny

Not applicable
Author

that's really clever!

Man, thank you so much for doing it!

now the question remains....can we regenerate the Subfileds expressions for all linkedIDs by using some iterative functions like ValueMap or not.

Right now, in this data set we have only 3 linkedIDs but in real I will have 1000s of linkedIDS. So practically it is not possible to write so many variables.

So the next that I am going to try is regenerate the SubField Expression for all linkedIDs.

sunny_talwar

Script is again not an option? or is it okay to create variables???

Not applicable
Author

Script is not an option for now.

Creating variables is ok