Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Not applicable

Load only the most updated record among multiple listed

Hi everyone,

I have an issue that I am hoping I can get some guidance and help with.  Basically, I am trying to match up sales information against quotes and their listed dollar amount.  They are on separate tables.  My issue is that for any given sales deal, there could be multiple quotes with multiple dollar amounts.  I am only interested in pulling the single, most updated quote.

Example:

Table 1 (Sales deals)

Sales Deal #
123
234
345
456
567
678
789

Table 2 (Sales Quotes)

Quote #
Sales #
Date Quote CreatedDollar Amount
11111111231/4/2012456.00
11111121235/3/20123324.00
11111132343/5/20124534.00
111111423412/4/20114443.00
11111152347/5/20128988.00
11111163455/4/20127788.00
11111173455/7/20127650.00

Sales # is linking the two.  I've highlighted in red the lines of data I'm interested in capturing.

Help is appreciated!

3 Replies
Highlighted
nagaiank
Valued Contributor III

Re: Load only the most updated record among multiple listed

You can get the highlighted rows using the following script:

load [Sales#],

     FirstSortedValue([Date Quote Created],-[Date Quote Created]) as [Last Quote Date],

     FirstSortedValue([Quote #],-[Date Quote Created]) as [Last Quote],

     FirstSortedValue([Dollar Amount],-[Date Quote Created]) as [Last Dollar Amount]

     Resident [Table 2 Sales Quotes]

     Group By [Sales#];

Highlighted
Not applicable

Re: Load only the most updated record among multiple listed

Thank you so much for the help!

I do have one issue that occurs when doing what you have listed above.

It appears as though I am still retrieving the other Quote #'s that I do not want included in my data set.  What your listed change has done when I add it in my script is simply take the [Quote #] that is the most recently created based on [Date Quote Created], and append the [Dollar Amount] for that quote to every single quote linking to that [Sales Deal #] field.

So, in effect, if a single [Sales Deal #] has 4 quotes with different dates and dollar amounts, that change above is just pulling and changing the data of the other quotes that I don't want pulled to match the quote I want to pull.  Instead, I want to remove all of the other [Quote #] and pull in only the single, most updated Quote #.

Help is very appreciated!!

Highlighted
Not applicable

Re: Load only the most updated record among multiple listed

Hi,

Try this

Load

Lastvalue(Quote#),

Sales,

Lastvalue([Date Quote Created]),

Lastvalue([Dollar Amount])

Resident [Table 2 Sales Quotes]

Group by [Sales#] Order by [Date Quote Created]);

Regards,

Xue Bin