Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
Is this coming from database? May be write a query which only brings the data you need?
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;
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);
I don't think I follow... would you be able to explain this via an example?
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?
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.
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
Hello,
No, I appreciate the responses but, nothing worked out for me.
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