Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
neena123
Partner - Creator
Partner - Creator

How to get the last sale date for an item without doing a resident table?

Hello,

I am trying to get the latest date sold for each item but without a resident table. Is there a way to just grab the data straight from the source without having to load all the data and then create a Resident table with the firstsortedvalue function? Reason being my company has many clients and bringing in ALL items for the past 10 years is a bit much. 

What the requirement is to have a table box that just has the list of items and the most recent date the item was sold. 

I literally have 3 fields I want to bring in

Client, Item, Date 

I have tried Date(MAX(Date) and it does not like it. 

 

Thoughts? 

Labels (3)
9 Replies
sunny_talwar

Is this coming from database? May be write a query which only brings the data you need?

manoranjan_d
Specialist
Specialist

Give dim as Item and exp as max(date) its worked for me with my tested data.atached the qvf

 

or in the script level of extraction use grup by concept 

 


LOAD
Item,Client,
max("Date") as Date
FROM [lib://test/Book1.xlsx]
(ooxml, embedded labels, table is Sheet1)
group by Item,Client;

neena123
Partner - Creator
Partner - Creator
Author

I did try that but I am not getting all my items. Some clients have items that are not sold everyday but I am pulling in only the items with the max date which is 11/19/2019. There should be some items with a sold date on 11/15/2019 (Friday) as well. 

S:
LOAD
SDITM,
IF(SDDGL <>0,Date(MakeDate(If(len(SDDGL)>5, 1900+ left(SDDGL ,3), 1900+left(SDDGL ,2))) + (right(SDDGL,3)-1))) AS SDDGL,
SDKCOO
;
select *
FROM  F4019 A
WHERE A.SDDGL = (select max(B.SDDGL) from F4019 B where B.SDKCOO = A.SDKCOO);

sunny_talwar

I don't think I follow... would you be able to explain this via an example?

neena123
Partner - Creator
Partner - Creator
Author

Sorry! I should of explained it better. 

Here is how the table is currently set up in my data source

Client Item Ship Date

A ball 11/15/2019
A ball 11/19/2019
A bat 10/25/2019
A kite 03/12/2019

B doll 11/19/2019
B doll 07/16/2019
B ring 11/19/2019

C jump rope 04/06/2019
C wand 11/19/2019
C jump rope 11/12/2019

 

I want to just bring in the LATEST ship date PER item. So this is what I would like to see in Qlikview: 

 

Client Item Ship Date

A ball 11/19/2019
A bat 10/25/2019
A kite 03/12/2019

B doll 11/19/2019
B ring 11/19/2019

C wand 11/19/2019

C jump rope 11/12/2019

 

 My current script looks like this:

Item is SDITM Client is SDKCOO and Date is SDDGL. 

S:
LOAD
SDITM,
IF(SDDGL <>0,Date(MakeDate(If(len(SDDGL)>5, 1900+ left(SDDGL ,3), 1900+left(SDDGL ,2))) + (right(SDDGL,3)-1))) AS SDDGL,
SDKCOO
;
select *
FROM   F4019 A
WHERE A.SDDGL = (select max(B.SDDGL) from   F4019 B where B.SDKCOO = A.SDKCOO);

But this script isn't bringing in all my items per client. It is bringing in the max date for all my clients and items but if one item has a max date that equals today's date it ignores any item that has an earlier date. It looks like this:

A ball 11/19/2019

B doll 11/19/2019
B ring 11/19/2019

C wand 11/19/2019

It just brings in the item with the latest date. For client A, item bat and kite are ignored even though their dates are the last time they were shipped. 

Does that make sense? 

 

sunny_talwar

I am not a SQL expert, but I would give this a shot

SELECT A.*
  FROM F4019 A,
       (  SELECT CLIENT, ITEM, MAX (B.SDDGL) AS MAX_SDDGL
            FROM F4019
        GROUP BY CLIENT, ITEM) B
 WHERE A.SDDGL = B.MAX_SDDGL AND A.CLIENT = B.CLIENT AND A.ITEM = B.ITEM;

I wasn't sure what CLIENT and ITEM were called in your database... but I am assuming SDDHL is the ship date from your example. 

Brett_Bleess
Former Employee
Former Employee

Hey Neena, did Sunny's last post with SQL code work for you?  If so, do not forget to return to the thread and use the Accept as Solution button on his post to give him credit for the help and to confirm to other Community Members that it did work.  If you are still working on things, leave us an update on what you need.

Regards,
Brett

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.
neena123
Partner - Creator
Partner - Creator
Author

Hello,

No, I appreciate the responses but, nothing worked out for me. 

Brett_Bleess
Former Employee
Former Employee

Unfortunately I am no SQL guru either, you may want to try StackOverflow or some other site that specializes a bit more on that side of things to see if someone can come up with the correct syntax for you, as I am not sure you will get that here.  We'll see if having bumped things back up to the top of the list gets anyone else to have a look though.  Sorry I do not have anything better for you.

The one place  you could look though is the following:

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

Not sure if there is anything along these lines there or not, but worth a search there, you may run across something that gives you another idea...

Regards,
Brett

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.