How to get pivot table showing 'missing' dates as 'x' instead of '-'

[DutchArjo]

I have a table with peoples presence at certain dates. Of course, sometimes people are not available and they are missing in the input table.

I want to create an overview, showing when people were present or not with the specific dates in the columns and as values either "v" (when present) or "x" when absent.

I thought I learned how to do so in the Udemy Qlik Sense course, but apparently, I haven't been paying enough attention or I am applying it in the wrong way.

So can someone help me with the right approach?

In the source table, presence is noted like below:

So for every single person, the presence is noted per date. People who were not present are no noted for that date.

I created the pivot like this:

On the left are the names and registration numbers of the people.

I created a new measure for the pivot with this expression:

if(count(appel_datum)>0,'v','x')

But as you can see only the dates when people are present are in the pivot. When people were not present, there is only the dash sign.

I also used tried this expression:

if(isnull(appel_datum)<>-1,'v','x') (unchecked 'show nulls').

How can I get Qlik Sense to show an "x" for the date a person was not present (and therefore is not in the source table)?

[greenee]

You won't be able to process it like this using an expression, because there is no data to process in the fact table for the people and the dates. 
You have to Fake the data in its creation for each date present in the Fact table.

I presume you have at least appel_datum fully populated and other fields like PersonID or similar not populated in the fact table.
So you will probably have to reprocess the fact table with something like
(Instead of (None) you can put the values of the data you require for each field.)

Data:
Noconcatenate
Load appel_datum,
If ( isnull(PersonID) or Len(PersonID)<1,'(None)',PersonID) as PersonID,
If ( isnull(<Fieldname>) or Len(<Fieldname>)<1,'(None)',<Fieldname>) as <Fieldname>,
//Etc, Etc, for all the fields you need

Resident Original_Loaded_Table;
Drop Table Original_Loaded_Table ;

Then you can do your expressions as you will have a full data set. 

****Hope this resolve your issue.
If the issue is solved please mark the answer with Accept as Solution & like it.****

Regards
greenee

[SunilChauhan]

try this

https://community.qlik.com/t5/New-to-Qlik-Analytics/Replace-missing-with-Data-Missing-N-A/td-p/17729... 

[DutchArjo]

my data model is now like this:

the fact table in this case is the table 'appél en is loaded with data like below:

I think its more that peopleID's are fully populated but dates are missing sometimes.

For some people, all dates are missing.

so first, it looks at the peopleID's and then it checks which date the person was presence. which I can check based on the dates available in the table yet.

So in fact I need a column 'presence'  either when a which is populated based on the data which are in the original table.

I am thinking that 2 lists should be build:

1 list of all dates present in the table

1 list of all Peopleid's present.

And then check if each ID is present in combination with the date.

Should this be done in some 'for  each date in appel_datum

for each peopleID in PeopleID's

if(isnull(peopleID),"x","v")

Next id

next date' routine?

Because when I loop through all the dates in the table and after that through all peopleID's, I still get the same. It just the combination of date plus people ID that is missing that I'm looking.

So in the image above, the id ending with 36 is missing voor 23-05-2024. But how do I get that result using a script?

create a list of all dates available and then run through the list of id's to find which date is missing per id?

[DutchArjo]

I have been trying for days now but I do not get anything near the solution.

I hate to admit it but I need more help.

the solution above just gives me the table I already have.

I have the date table with the dates, so I have 3 unique dates showing in that table:

21-5-2024, 22-5-2024, 23-5-2024.

I can load this distinct dates in a separate table called 'Dates'.

And I have 10 id's with varying dates in a list:

I have loaded this list in the table "MED_AANWEZIGHEID"

How can I get a value for each ID in combination with every date?

Many thanks for your help.

[Anil_Babu_Samineni]

From the table given above, what is the output you are expecting?

[DutchArjo]

I want to have a table where every ID is matched to every date. and if the combination of ID and date is not in the source table, in a column called aanwezigheid an "x" is placed and when the combination is in the source  table, an "v"  is placed:

[Anil_Babu_Samineni]

My suggestion to add E column in excel with output and share it to us so we will see. 

[DutchArjo]

 There is nothing in Column E. I just created a visual example of the output I wanted as requested in the worksheet I have the raw data in.

All I have is what I have attached to this reply.

[DutchArjo]

I have realised what I wanted to realise:

I used this load script:

AFWEZIGHEID:
NullAsValue MDW_afwezigheid;
set NullValue = 'x';

Load
trim([%MDW_ID]&'_'&date(afwezig_datum)) as DatumSleutel,
afwezig_datum as MDW_afwezigheid

FROM [lib://selfservice_29_LBO/BE format/Arjo_Kopie van BE format  2024 test_.xlsx]
(ooxml, embedded labels, table is mdw_afwezigheid);

AANWEZIGHEID:
lOAD
[ID] as [%MDW_ID],
datum as ANW_datum

FROM [lib://selfservice_29_LBO/BE format/Arjo_Kopie van BE format  2024 test_.xlsx]
(ooxml, embedded labels, table is test_dat);

zTemp:
load distinct ([%MDW_ID]) resident AANWEZIGHEID;
for each a in FieldValueList('%MDW_ID')

        for each b in FieldValueList('ANW_datum')
           
            load '$(a)'&'_'&'$(b)' as [DatumSleutel] Autogenerate 1;
        next b
next a

MDW_AANWEZIGHEID:
lOAD*
inline [
DatumSleutel, ANW_Date
];


zTemp2:
Concatenate (MDW_AANWEZIGHEID)
Load
[DatumSleutel],
subfield(DatumSleutel,'_',1) as [%MDW_ID],
DATE(subfield(DatumSleutel,'_',-1),'DD-MM-YYYY') as ANW_Date,
if(isnull(lookup('MDW_afwezigheid','DatumSleutel', DatumSleutel,'AFWEZIGHEID'))=-1,'1','0') as ANW_Presentie
resident [AUTOGENERATE(1)];

store MDW_AANWEZIGHEID INTO [lib://selfservice_29_LBO/BE format/MDW_AANWEZIGHEID.QVD] (qvd);



drop tables zTemp, [AUTOGENERATE(1)];


exit script;

created a  pivottable

and for the value/measure I used this expression:

If(ANW_Presentie=1,'v','x')

and for 'Background color expression':

if(ANW_Presentie=1,'Green','Red')