topic Re: Find last string and show value, if string not found show value 0 in QlikView

Find last string and show value, if string not found show value 0

Anonymous — Mon, 31 Aug 2015 14:23:39 GMT

Hi Experts, im generating a inventory overview where in a chart the historical figures are shown and another chart is showing the actual stocks, the actual stock should show just the last stock figures (29.08.15)

The inventory history is working fine, no issue... But im struggling a bit wo show the actual stocks.

Defintion how the sql server is loaded with the our stock.

1. Normally with a daily frequency (sometimes just after 2 or 3 days, thats teh reason im showing the stock date on the actual stock information)

2. If a stock value is 0, no data will be written to the database

3. History, is stored once a month (later once a week9

My idea was to look generally after the last inventory file update (string) and check whether on that specific date, the item has recorded some stock. If nothing is found the value 0 should be displayed.

To find the last string i made this expresion, which gives me the correct date information back.

MaxString ({1<#InventoryOnHand+={1}>}Date)

value is 29.08.2015

What i like to do now is, if a item hast stock information at the 29.08.15, i would like to display that value, if no information are found on the item, the value should be zero.

Can somebody help me out

Thanks

Re: Find last string and show value, if string not found show value 0

Gysbert_Wassenaar — Mon, 31 Aug 2015 14:41:09 GMT

Create a variabel vMaxDate: =MaxString ({1<#InventoryOnHand+={1}>}Date)

And an expression that uses that variable alt(sum({<Date={'$(vMaxDate)'}>}InventoryOnHand),0)

Re: Find last string and show value, if string not found show value 0

Anonymous — Mon, 31 Aug 2015 17:59:37 GMT

Hi Gysbert

Thanks for support

Did try your solution, just have a problem that zero stock items arent displayed. Lets say i have an assortment over 100 items, if 20 items have no stock, im going to show just 80 items in total. Target ist that i show 100 items, 20 of it with value zero.

Another problem is, if any filter is set on the date, the items are going to disapear as well

Thanks

Re: Find last string and show value, if string not found show value 0

Gysbert_Wassenaar — Mon, 31 Aug 2015 18:13:16 GMT

Make sure to disable the Suppress Zero-Values option on the Presentation tab,

Re: Find last string and show value, if string not found show value 0

Anonymous — Mon, 31 Aug 2015 18:34:35 GMT

Perfect!

Full assortment is diplayed

Date selection hast no influence on the actual stock overview

No double counts on items

Many thanks, just great your support