topic Re: use Lookup to find value ? in QlikView

use Lookup to find value ?

QFanatic — Sat, 16 Nov 2024 18:30:45 GMT

hi Everyone

PFA.

as you can see from the chart there is no data for Day 10 of April and May.

I would like to insert Day 10 into the chart - with no data. Its much easier to see there's missing data for the 10th, in that way.

In my model, the Key field that joins the fact table to the Calendar, is TRAN_DATE. To make it visible that the 10th is missing, I'd need to "manually insert" at least, a Zero volume row for that date.

I thought of looping through all the records in my table, looking up the 'NON Key' field TRAN2_DATE, and then do the 'forcing in' there.

I'm not sure if that idea would work - or if my Lookup statement here at all is 1) the right thing to do 2)actually does the right thing.

All input would be welcome thanks

Re: use Lookup to find value ?

sunny_talwar — Wed, 10 Jun 2020 12:54:45 GMT

Changing your expression to this seems to be helping

If(Sum({<PERIODID = {$(=Max(PERIODID)-2)}, Table = {"Table1"}>} TRN_AMOUNT) + Sum(0) = 0, 'N/A', Sum({<PERIODID = {$(=Max(PERIODID)-2)}, Table = {"Table1"}>} TRN_AMOUNT))

and

=If(Sum({<PERIODID = {$(=Max(PERIODID)-1)}, Table = {"Table1"}>} TRN_AMOUNT) + Sum(0) = 0, 'N/A', Sum({<PERIODID = {$(=Max(PERIODID)-1)}, Table = {"Table1"}>} TRN_AMOUNT))

Re: use Lookup to find value ?

QFanatic — Wed, 10 Jun 2020 14:13:17 GMT

Awesome as always, Thank you Sunny!

I have another model which has a similar problem, but this proposed solution, doesnt work there. I will create a new post for that and Tagg you.

Much appreciated

Re: use Lookup to find value ?

sunny_talwar — Wed, 10 Jun 2020 14:32:58 GMT

Sounds good

Re: use Lookup to find value ?

QFanatic — Wed, 10 Jun 2020 15:33:11 GMT

hi Sunny

Here is the link of my new post - tried implementing your suggestion here as well - no luck 🙂

Much appreciated

https://community.qlik.com/t5/New-to-QlikView/Display-all-dates-of-Month/m-p/1709830#M390103