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: 
JoepF2
Partner - Contributor
Partner - Contributor

Only the most recent line

Hi,

Excuse me if I might have used the wrong location.

My question is about three dimensions which show the price of the rentalcontract over the years. My wish is that the dimensions only show the most recent year/pricerow, which is €61.211,04, €193,20 & €61.404,24. (see picture below). Now it shows all of the price history, but I only want the most recent (or current) price.

JoepF2_1-1677747191050.png

JoepF2_0-1677747117701.png

What is the most efficient way to generate this wish? I have little to less experience with programming. What information do you need from me to generate the formula?

Thanks in advance.

 

 

 

Labels (1)
7 Replies
Gabbar
Specialist
Specialist

Knowing the names of column would help a lot,
but let me try here
these are prices so lets name these three field A,B,C
Now these are prices of something  suppose that thing details is in Field D (These can be multiple column rather than single you can consider all of those merged here using '&')
Now as you have asked for latest Price only then there must be date field in the Table lets name that column F.
So try this:
Table1:
Load A,B,C,D,E ,A&E as Key from Source;
inner join
Load A&E as Key;
Load A,max(E) as E resident Table1 group by A; 



JoepF2
Partner - Contributor
Partner - Contributor
Author

Hi @Gabbar ,

Thanks for replying.

The first column is called: =[Aanhuur.NettoHuur]
The second column is called: =[Aanhuur.Servicekosten]
The third column is called: =[Aanhuur.BrutoHuur]

The columns are indeed linked by a date field, which is called: =[Aanhuur.DatumVan]

Could you perhaps repeate yourself and the steps with the column names?

 

Gabbar
Specialist
Specialist

there must be 1 more column, These are prices of something(like Bread,Electronics etc) what is the name of that column.

JoepF2
Partner - Contributor
Partner - Contributor
Author

@Gabbar 

It's linked to the column: =[Huurinfo.Huurcontract]. It's the contractnumber.

Gabbar
Specialist
Specialist

Are all these field same table,
Please add a snapshot of Table of all columns .

JoepF2
Partner - Contributor
Partner - Contributor
Author

No it's not.

Here's the snapschot:

JoepF2_0-1677834853078.png

 

Gabbar
Specialist
Specialist

Snapshot of table as sample data, Not load editor,:
Let me try again :
The field name is Objects whose prices are given for different time period.
Now:
Table1:
Load Objects,[Aanhuur.NettoHuur],[Aanhuur.Servicekosten],[Aanhuur.BrutoHuur],[Aanhuur.DatumVan]
,Objects&[Aanhuur.DatumVan] as Key from Source;
inner join
Load Objects&[Aanhuur.DatumVan] as Key;
Load Objects,max([Aanhuur.DatumVan]) as [Aanhuur.DatumVan] resident Table1 group by Objects; 

This Data is of something, (Mostly The primary key of table).