Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
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..
How would i go about loading in dummy dates in my script?
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
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:
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:
As you can see, that removes the ? but no new date of 01/01/80 is shown.
Have i done something wrong?
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,
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
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
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...
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
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.