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: 
hopkinsc
Partner - Specialist III
Partner - Specialist III

Help with expression for showing latest details

Hi, i am trying to display the current stock level for every product (for the selected department) for every store.

I have had some help with this before but still can't get it right.

My chart currently shows multiple lines for most of the products (this is because every store sends in a file every night detailing their stock details, so QLIKVIEW is showing me every occurence of the product). I only want to see the last file date that was processed (the field is StoreDate).

I want to see 1 line for each product showing the last file processed with the latest LastSoldDate but it has to display the accurate StockLevel for each product.

I have attached a sample.

Can anyone help please?

47 Replies
swuehl
MVP
MVP

Hi,

I've posted an expression that should work to filter out duplicates in my previous post, please try.

You can replace the '?' with a dummy date on load, if that's an option.

My question was a little different:Is it possible, that you have two store dates say, 01/01 (DD/MM) and 02/01, but 01/01 holds the LastSoldDate that is more recent than within 02/01, i.e. the LastSoldDate is being corrected back in my understanding of the data? If this is not possible, I think we could simplify the expression.

Regards,

Stefan

hopkinsc
Partner - Specialist III
Partner - Specialist III
Author

Hi Stefan,

A newer StoreDate should always have the latest LastSoldDate or the same LastSoldDate as older StoreDates. It will never have an older LastSoldDate.

the last expression you gave still shows duplicates as shown below..

untitled.JPG

How would i go about loading in dummy dates in my script?

swuehl
MVP
MVP

I think it could be like:

LOAD

...

if(LastSoldDate='?',MakeDate(1900,01,01),LastSoldDate) as LastSoldDate,

...

SQL SELECT *

from ...

(given that LastSoldDate is parsed in as Date Format, otherwise use your current expression from your load  script for the else part of the if clause.)

edited by swuehl

hopkinsc
Partner - Specialist III
Partner - Specialist III
Author

The LastStoreDate field is loaded in as a date format automatically.

my old script was:

StoreProduct:

LOAD

        LastSoldDate,

     %LkStoreEAN

    

FROM $(cFileName) (qvd);

and that gave me this LastSoldDate ListBox:

old.JPG

I have just tried :

StoreProduct:

LOAD

     if(LastSoldDate='?',MakeDate(01/01/80),LastSoldDate) as LastSoldDate,

    //LastSoldDate,

     %LkStoreEAN

    

FROM $(cFileName) (qvd);

and that gave me the following list box:

old.JPG

As you can see, that removes the ? but no new date of 01/01/80 is shown.

Have i done something wrong?

swuehl
MVP
MVP

I believe MakeDate's argument are Year,Month, Day not Month,Day,Year. Also comma separated and full year.

So I think it should be

if(LastSoldDate='?',MakeDate(1980,01,01),LastSoldDate) as LastSoldDate,

hopkinsc
Partner - Specialist III
Partner - Specialist III
Author

Hi Stfan, Sorry to be a pain...

I have decided to remove the field [LastSoldDate] now as it is causing too many problems. So for the time being i just want the application to display the last date processed.

Maxstring(StoreDate) will give me the latest date, but i am unsurfe how to get the StockLevel related to that date..

Can you help please?

Thanks

Chris

swuehl
MVP
MVP

Hi Chris,

I think we still may run into trouble, since we have multiple StoreDates with different StockLevel.

e.g.

StoreNumber 17749

Product Code 548554

StoreDate 26/07/11

with Stock Levels 0 and 2

Which one to pick?

Do we have another information which one the most recent information is?

Regards,

Stefan

hopkinsc
Partner - Specialist III
Partner - Specialist III
Author

Ok, i have just been looking into why there are multiple stock levels for some products.

There will be multiple if there are more than one code for the same product..

eg

   DATE      P-CODE     DESC                    STOCK LEVEL

26/07/11      548554      CLIPPER REFIL                    0

26/07/11      547146      CLIPPER REFIL                    2

The above products are exactly the same product but with different PRODUCT CODES (it usually means that one of the products are price marked)

for this a total of the stock level would be needed.

Sorry, i completely didn't think of linked products...

So what i need then is to show the latest date with a sum of the stoick level for that date...

swuehl
MVP
MVP

Chris,

you are saying that there are multiple Product Codes. So for summing up, how do you decide which are the "same product"? By DESC?

But as I said, in the data of the application we shared some days ago, I found one P-CODE with multiple Store Date entries for the same store date. Shall we just sum those up, too?

I'm getting a little confused

Regards,

Stefan

hopkinsc
Partner - Specialist III
Partner - Specialist III
Author

Stefan, sorry for confusing you...

Yes, there are multiplle product codes for the same products, they are identified as the same product by EAN code.

Basically, if any changes are made by the manufacturer, i.e new packaging, Price marked pack, different pack size etc, then a different Product code is given but they would all share the same MAIN CODE and EAN.

example..

M-Code  P-Code         EAN                                Desc

PK1899 PK1036 5060037811036 00 301 UMBRELLA PLAIN COLOU

PK1899 PK1899 5060037811036 00 301 UMBRELLA PLAIN COLOU

As you can see, the MAIN CODE is the same for both products.

In my application i am only loading in MAIN CODES. PRODUCT CODE is being loaded in but onlyu the MAIN PRODUCT CODES (the MAIN PRODUCT CODE is the product with a matching M-Code and P-Code, so the bottom line in the above example)

That is why you are only seeing ONE product code.