topic Total Modifier Question in QlikView

Total Modifier Question

Mon, 04 May 2015 21:23:55 GMT

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

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

Re: Total Modifier Question

ramoncova06 — Mon, 04 May 2015 21:33:52 GMT

in your total add the the field grouping <ID>

is like this

sum(total <field> metric)

Re: Total Modifier Question

Mon, 04 May 2015 21:39:52 GMT

Sorry but i did not understand.

Can you please give me the full expression?

Re: Total Modifier Question

ramoncova06 — Mon, 04 May 2015 21:46:10 GMT

This will do the grouping by ID, for your total

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

Re: Total Modifier Question

Mon, 04 May 2015 22:01:10 GMT

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

Re: Total Modifier Question

ramoncova06 — Mon, 04 May 2015 22:26:02 GMT

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

Re: Total Modifier Question

sunny_talwar — Tue, 05 May 2015 10:10:22 GMT

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

Best,

Sunny

Re: Total Modifier Question

Tue, 05 May 2015 13:48:15 GMT

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.

Re: Total Modifier Question

sunny_talwar — Tue, 05 May 2015 13:51:44 GMT

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

Re: Total Modifier Question

Tue, 05 May 2015 14:20:20 GMT

Script is not an option for now.

Creating variables is ok

Re: Total Modifier Question

sunny_talwar — Tue, 05 May 2015 14:21:27 GMT

creating variables in the script?

Re: Total Modifier Question

Tue, 05 May 2015 14:36:29 GMT

Not in the scripts but in the expressions.

BTW I am trying to hold of the scripts so I can make changes there. Not sure if I will get it but I would also love to get your suggestions for changing scripts.

Re: Total Modifier Question

sunny_talwar — Tue, 05 May 2015 16:26:32 GMT

I will be more than happy to assist you working with a script. But unfortunately I don't know of a way to create variables in bulk on the front end. or even looping through them.

You mentioned ValueMap function. I have not heard of this function before, would you be able to give an example of how it works (may be we can together get a solution for you )

Best,

Sunny

Re: Total Modifier Question

Tue, 05 May 2015 17:23:50 GMT

Here is one application which has a good sue of ValueMap and Pick function.

About the script...yeah sure...

Thanks for offering the help.

The ApplyMap is one way to go but I would like to know if you have any other suggestion.

Re: Total Modifier Question

sunny_talwar — Tue, 05 May 2015 17:25:26 GMT

you meant ValueLoop

Re: Total Modifier Question

Tue, 05 May 2015 17:26:44 GMT

oh Yeah