1

Suppose I have the following very simple data warehouse:

section application;

car:
LOAD * INLINE
[
CAR,USER
GETZ,George
];

person:
LOAD * INLINE
[
USER
George
Maria
];

As you can see, USER George has a CAR GETZ, while USER Maria has no car.

Is it possible to set the section access up, using reduction field=CAR, so that a user can only see Maria, who has a null value on CAR?

I have read COUNTLESS posts. Many say to put an empty string- that doesn't work. I have also seen multiple "Answers" having sample qvds attached - and most of them use a single table with an empty string value to make the test, which then seems to work with the empty string value at section access. Of course, this is not the case I'm interested, as the empty string value is still different than a missing/nulul value

George Menoutis
  • 6,894
  • 3
  • 19
  • 43

1 Answers1

0

Because section access works via associations in Qlik it wouldn't be possible with your current data model. Maria doesn't have a null car - she has no car association (which will show as a null if you put USER and CAR in a table but it's a subtle difference). If structured your data a little differently you could potentially make it work with a blank... something like

car:
LOAD * INLINE
[
CAR,USER
GETZ,George
];

outer join(car):
LOAD * INLINE
[
USER
George
Maria
];

You might need to populate the null values with blank afterwards...

car2:
NOCONCATENATE LOAD
if(isnull(CAR),'',CAR) as CAR,
USER
Resident car;

drop table car;

If it's not possible to join the two tables then you could add blank entries to your car table?

car:
LOAD 
*,
USER as USER2 
INLINE
[
CAR,USER
GETZ,George
];

person:
LOAD * INLINE
[
USER
George
Maria
];

concatenate(car)
LOAD 
USER,
'' as CAR
RESIDENT person
WHERE NOT EXISTS (USER2,USER);

There are also options with some kind of combined key or applymaps potentially, but hopefully one of the above is helpful enough...

x3ja
  • 928
  • 5
  • 19
  • Your answer is generally towards adding a "dummy row" in the car table. I don't like this, it falsifies the data (eg: count(distinct CAR) for Maria should return 0, not 1). Is there no other way? Am I correct to think this is a major oversight? – George Menoutis Jul 13 '18 at 11:00
  • It's not an oversight - it's to do with association - there is no value for CAR associated with Maria so you can't link on that data - whether for section access or just in the data model itself. What you're asking for is to be able to link to Maria based on a link that isn't in your data model - so you have to create that link somehow or find another way to do what you want. The first one I propose doesn't really fake any data - it doesn't add any rows - it just populates a table with null or blank for CAR by joining the tables. The second one does indeed make new rows though. – x3ja Jul 13 '18 at 16:24
  • I accepted your answer, but I still think it's an oversight. If the qlik engine is able to show null values in a chart table (and by extension show non-null results on null values by mean of an if(isnull(...)) function), it would be perfectly -and easily- possible to handle the null values without having to resort to manual joins in the script. – George Menoutis Aug 29 '18 at 11:14