topic Re: Show in QlikView

Show

Thu, 29 Jan 2015 12:57:25 GMT

Hi guyz,

Here is m problem :

AS you see, I have for a year an amount. I would like to show only the last year for un ID.

In this case, i would like to show 2010 -> 0,00.

Witch function i have to use ? (rank ? maxstring ?)

Thx in advise

alexandros17 — Thu, 29 Jan 2015 13:06:37 GMT

When you sum Amount:

Sum({$ <Year={'$(=Max(Year))'}>} Amount)

let me know

Thu, 29 Jan 2015 13:36:45 GMT

Hi Alessandro,

I think you miss understand me. I don't want to see the amount of the last year.

I always want to see, on my tab the 4 column for the Max(Year) of the line.

Example :

I want something like this :

ID - NB PERSON - AMOUNT - YEAR

01010001 - 2 - 0,00 - 2010 (because it's the last year for this ID)

01010002 - 1 - 22 412,00 - 2013 (because it's the last year for this ID)

etc ...

Maybe I have to modify something in my load ?

sunny_talwar — Thu, 29 Jan 2015 13:49:41 GMT

You can try and flag the MaxYear for each ID in the script and then use that in your set analysis statement.

Best,

Thu, 29 Jan 2015 13:51:10 GMT

I'have find a solution with SQL :

1 - I do a rqs for ID and MAX(Year)

2 - I do a rqs for ID, NB PERSON, AMOUNT, MAX(YEAR)

3 - I join the 2 table with ID=ID and MAX(YEAR)=MAX(YEAR)

So I have all the data for the max(year) in 1 line

sunny_talwar — Thu, 29 Jan 2015 13:54:43 GMT

Join(yourTable)

LOAD ID,

MAX(Year) as Year,

1 as MaxYearFlag

Resident yourTable

Group By ID;

This will add another column to your table where ID and its corresponding Max(Year) match.

Hope this will help.

Best,