topic Re: Work-Around for QlikView Associative Logic in QlikView

Work-Around for QlikView Associative Logic

lucasdavis500 — Wed, 25 Nov 2020 16:16:04 GMT

I'm trying to work-around, but work with ( I think? ), Qlikview's associating logic. It's hard to explain without an example, so here's one..

I have some data: Some dates, Some Pkey, and Some Account #'s

Below data(DATE FIELD) is in a ListBox: Below Data is in a TableBox:

DateDue: Pkey MonthCreated AcctNum DateDue
11/3/2016 1234 Oct 4567 11/3/2016

11/4/2016 2345 Oct 2345 11/4/2016

11/5/2016 3456 Oct 2589 11/5/2016

11/6/2016 4567 Oct 7845 11/6/2016

My user has a list box (on the left) that they can select a date (DateDue), When they make a selection, my TableBox (Right) shows a list of all of my AcctNum's with a due date of that date (usually that day or the next day). This is great, it is easy to find Account Numbers by their due date to prioritize. The problem, however, is I would like my user to know when there are more Accounts due on a different date than that particular due date, and have a different Pkey, but the same AcctNum. Ideally, when the user selects the DateDue, they would have a list of all of the accounts that are DUE on that Date, PLUS a list of accounts that correspond to the SAME Account Number.

I'm sure this is possible with qlikview, I just don't know how to stop QV from associating those DateDue's and Associate the AcctNum...

Re: Work-Around for QlikView Associative Logic

sunny_talwar — Thu, 03 Nov 2016 19:51:46 GMT

So based on the data you have provided, what would be an output based on selection? Or is this not a good database to point that out? I would say provide a sample with expected output and let us play around with it a little bit.

Best,

Sunny

Re: Work-Around for QlikView Associative Logic

lucasdavis500 — Thu, 03 Nov 2016 20:07:34 GMT

Thanks Sunny,

Going off of my example above:

DateDue: Pkey MonthCreated AcctNum DateDue

11/3/2016 1234 Oct 4567 11/3/2016

11/3/2016 7894 Oct 7895 11/3/2016

Assuming the user clicks 11/3/2016 on the left in the listbox. Usually there are many options, but here there are two options available on that due date. This is great for filtering what Accounts are due that day, but let's say the user, in the context of efficiency, want's to clear up all of the data in the queue for that specific Account. Qlikview is only showing AcctNum based on that DateDue.

What if, our data looks like this..

DateDue: Pkey MonthCreated AcctNum DateDue

11/3/2016 1234 Oct 4567 11/3/2016

11/3/2016 7894 Oct 7895 11/3/2016

11/15/2016 5894 Oct 4567 11/15/2016

If the user selects DateDue 11/3/2016 from the list box on the left, the third observation disappears. I would like this observation to show either A) within the same table box, just as a subset(?)/cascaded values underneath, displaying the Pkey and the DateDue, or B) have another table box/ view available that would show all AcctNum's that correspond to any given AcctNum on the date the user selects..

I hope this makes sense..

Re: Work-Around for QlikView Associative Logic

johnw — Thu, 03 Nov 2016 20:20:30 GMT

Not sure if it's exactly what you want, but perhaps use this set expression in any aggregations in the chart?

{1<AcctNum=P()>}

See attached example.

Re: Work-Around for QlikView Associative Logic

lucasdavis500 — Thu, 03 Nov 2016 20:27:17 GMT

To elaborate further, so, when a user selects the DateDue, One box would show a list of all AcctNum's, with their corresponding Pkey, that are due on that day. On the other hand, in another box, would be a list of all of the AcctNum's that were contained in the first box after the user made their selection, but the DateDue may not necessarily be the same DateDue the user selected, as well as having different Pkey's..

Re: Work-Around for QlikView Associative Logic

lucasdavis500 — Thu, 03 Nov 2016 20:42:16 GMT

John, this is great. I have never heard of the P() function and will have to research it, your formula seems to be working for your data, however it looks like the expression is read by QV as an error, is this a bug? I'm wondering how your formula works when it has errors in the syntax. Also, I am not trying to return a sum of anything, just a list of AcctNum's with their corresponding Pkey back...

Re: Work-Around for QlikView Associative Logic

kamielrajaram — Thu, 03 Nov 2016 21:22:38 GMT

Hi,

Add a straight table with the following expression count(if(NewDueDate >=DueDate,PKey)). Add in DueDate again to your table when loading with the alias NewDueDate.

Kamiel

Re: Work-Around for QlikView Associative Logic

johnw — Thu, 03 Nov 2016 21:29:05 GMT

Yes, QlikView has bugs where the editor doesn't recognize the syntax, even though it's valid. In mine, at the top it says that the expression is OK, even though it's highlighting parts of it as bad.

I guess you could move the AcctNum field from being a dimension to being an expression? Yeah, that seems to work.

only({1<AcctNum=P()>} AcctNum)

Re: Work-Around for QlikView Associative Logic

sunny_talwar — Thu, 03 Nov 2016 23:30:52 GMT

Does it still show the red wiggly line if you do this John?

Only({1<AcctNum=P(AcctNum)>} AcctNum)

Re: Work-Around for QlikView Associative Logic

johnw — Thu, 03 Nov 2016 23:35:27 GMT

The wiggly line goes away then. My reading of the set analysis help text suggests the field name is optional, though?

( P | E ) ( [ set_expression ] [ field_name ] )

Re: Work-Around for QlikView Associative Logic

sunny_talwar — Thu, 03 Nov 2016 23:37:23 GMT

Oh ya, it is optional. This was just to get rid of the wiggly line

Re: Work-Around for QlikView Associative Logic

johnw — Thu, 03 Nov 2016 23:40:16 GMT

Ah, OK. I've just learned to ignore the wiggly lines. QlikView just isn't fond of the way I write my expressions.

Re: Work-Around for QlikView Associative Logic

lucasdavis500 — Fri, 04 Nov 2016 13:42:48 GMT

Sunny, that did work

Do you know how I can do this to include only the last 60 days? I have this expression...

=Only({1<Month = {"$(>=Month(Today() - 60))"} , AccountNumber=P(AccountNumber)>} AccountNumber)

but this doesn't seem to work....I thought placing the GT equal to symbol in set analysis was correct

Re: Work-Around for QlikView Associative Logic

sunny_talwar — Fri, 04 Nov 2016 14:46:04 GMT

Try one of the two options

1) Only({1<Month = {"$(='>=' & Month(Today() - 60))"} , AccountNumber=P(AccountNumber)>} AccountNumber)

2) Only({1<Month = {">=$(=Month(Today() - 60))"} , AccountNumber=P(AccountNumber)>} AccountNumber)

UPDATE: Thing to make sure here is that if you have more than 1 year of data, the above expression will pick all year data from Sept, Oct, Nov, and December. Is this what you want or do you want just Sept, Oct, Nov data for this year?

Re: Work-Around for QlikView Associative Logic

lucasdavis500 — Fri, 04 Nov 2016 15:38:32 GMT

Good catch... the most current year..

Re: Work-Around for QlikView Associative Logic

lucasdavis500 — Fri, 04 Nov 2016 15:40:00 GMT

so probably add year = {(=TODAY(0))}? I want the most 30 days, from the most current year..

Re: Work-Around for QlikView Associative Logic

sunny_talwar — Fri, 04 Nov 2016 15:41:59 GMT

May be like this:

Only({1<Month = {"$(='>=' & Month(Today() - 60))"}, Year = {"$(=Year(Today()))"} AccountNumber=P(AccountNumber)>} AccountNumber)

Re: Work-Around for QlikView Associative Logic

lucasdavis500 — Fri, 04 Nov 2016 15:46:17 GMT

Right, but I see a problem arising in January, when they still need last 30 days, but last 30 days may be in the last year. But I also don't want the user to see consecutive September data from previous years.... do I have to pick the lesser of the two evils here?

Re: Work-Around for QlikView Associative Logic

sunny_talwar — Fri, 04 Nov 2016 16:02:53 GMT

No, the best option would be to get rid of month and year field and use dates to do the job for you

Only({1<Date = {"$(='>=' & Date(Today() - 60, 'DateFieldFornatHere') & '<=' & Date(Today(), 'DateFieldFormatHere'))"}, AccountNumber=P(AccountNumber)>} AccountNumber)

Re: Work-Around for QlikView Associative Logic

johnw — Fri, 04 Nov 2016 16:11:01 GMT

Or like this, avoiding date field formats and dollar sign expansions and doing a straight numeric compare, assuming I got the syntax correct. I'm, uh... syntactically challenged sometimes.

only({1<Date={"=Date>=today()-60 and Date<=today()"},AccountNumber=P()>} AccountNumber)