Work-Around for QlikView Associative Logic

[lucasdavis500]

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...

[sunny_talwar]

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

[lucasdavis500]

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..

[johnw]

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.

[lucasdavis500]

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..

[lucasdavis500]

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...

[kamielrajaram]

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

[johnw]

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)

[sunny_talwar · Accepted solution]

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

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

[johnw]

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 ] )