How to show the last column's value of a record?

[#1]

Hello all,

i have this situation :

ID | CUSTOMER | PRODUCT | STEP 1 | STEP 2 | STEP 3..... STEP 10

with such fields...

1 | 1 | 1 | 10/05/2009 | | | 12/05/2009
2 | 1 | 2 | | 12/05/2009 | | |
3 | 2 | 1 | 12/05/2009 | 13/05/2009 | 14/05/2009 | |

What i'm trying to obtain is :

1 | 1 | 1 | | | | 12/05/2009
2 | 1 | 2 | | 12/05/2009 | | |
3 | 2 | 1 | | | 14/05/2009 | |

what i need, is for 1 record, the "last" know value of each row.
How i can reach this situation?
Thanks for the help!

[johnw]

It's an ugly solution, but I'd think this would work without any assumptions about your dates:

STEP 1 expression: if("STEP 2"+"STEP 3"+...+"STEP 10",,"STEP 1")
STEP 2 expression: if( "STEP 3"+...+"STEP 10",,"STEP 2")
...
STEP 9 expression: if( "STEP 10",,"STEP 9")
STEP 10 expression: "STEP 10"

If the dates ALWAYS increase, you could define an "LAST STEP" field that is the maximum date of all the steps (i.e., rangemax("STEP 1","STEP 2","STEP 3"..."STEP 10")). Then your expressions would look like this:

STEP 1 expression: if("STEP 1"="LAST STEP","STEP 1")
STEP 2 expression: if("STEP 2"="LAST STEP","STEP 2")
...

And you could also assign these values in the script to save time building the chart, like having new fields "STEP 1 LAST" and so on.

I'm kind of hoping someone has a better solution, because I don't like mine.

[#3]

Hi,

first of all THANKS.
I worked on your solution and i reach finally to get the "last result" of a STEP.

BUT another problem now is on the way...

When i click on "STEP 4" ( as example ), it will not calculate all the data, but the only "STEP 4".
So, if i have this situation :

ID | ID_CUSTOMER | ID_PRODUCT | STEP 1 | STEP 2 | STEP 3 | STEP 4 | STEP 5
1 | 1 | 1 | | | | 12/06/2009 | 15/06/2009
2 | 1 | 1 | | | | | 14/06/2009
3 | 1 | 1 | 12/06/2009 | | | |
4 | 1 | 1 | | | | 13/06/2009 |

when i click on Step 4 actually i will have :

ID | ID_CUSTOMER | ID_PRODUCT | STEP 4
1 | 1 | 1 | 12/06/2009
4 | 1 | 1 | 13/06/2009

But this is wrong, becase the ID 1 product was on STEP 5 on 15/06/2009, so it hadn't to show.
I knew that this is a limitation, because QlickView will valutate only the filtered field, but i'm wondering if there's a way to solve this problem

Thanks to everyone that will try to help!

[stephencredmond]

Hi,

Could you use Cross Table to get the Step and Date into separate fields and then use FirstSortedValue to find out the last values for each Customer/Product?

Regards,

Stephen

[johnw]

OK, so you don't want the results to be sensitive to your selections? Is this just straight data, the kind you could load in the script? If so, you could apply the same expressions during the load, and then they wouldn't be affected by your later selections.

[#6]

Hi,
sorry if i didn't response earlier, but this weekend i just stop to think about it to relax myself 🙂

The data, isn't on a single table ( i got them from a no-relational database, _try_ to make a relational one, and use them on QlickView.
The real data of the table that i described earlier, are on 6 table, but the "STEP" and the "Date" are on a single table in two rows.

I don't understand what you mean for : "First Sorted Value" to find out the last values for each Customer/Product ....i need the entire workflow and not only one record for each customer/product 😕

i'll await your order 🙂

Thanks for now.

[#7]

If it's possible, i would just "subquery" the results of a Qlickview's table in order to show only certain data.
But on a course, the teacher said that isn't possible.

Now, my data comes from a "no relational data " and i don't know if i can re-apply a filter after the data load to change the selection.