topic Re: identify missing records using a table expression in QlikView

identify missing records using a table expression

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

I have a problem which i am hoping can be solved very easily using the great minds of the Qlik community.

I have checked the forums and can't quite see the solution i need so apologies if this has already been asked and answered.

I am loading two tables into a dashboard. The first table is a CURRENT view of the data and includes the following fields:

ID	Name	Status	Info
1	Record A	1 - Start	Record info stored here
2	Record B	4 - Live	Record info stored here
3	Record C	3 - Deploy	Record info stored here
4	Record D	2 - Build	Record info stored here

The second table is a HISTORY table and is appended once a week with a snapshot of current data:

Date	ID	Name	H_Status
01/01/16	1	Record A	1 - Start
01/01/16	2	Record B	1 - Start
08/01/16	1	Record A	1 - Start
08/01/16	2	Record B	2 - Build
08/01/16	3	Record C	2 - Build

The dashboard i have built uses the following calculated dimension (but could as easily be an expression i guess) and displays only records that "Y" is present:

=IF(Left([Status],1)>3 and (Left([H_Status],1)<4),'Y',)

This works fine to compare existing records however my issue is that if i have brand new records that are on CURRENT and do not yet appear in HISTORY they do not show on the dashboard view.

I am open to either amending the IF syntax in the dimension or adding a flag on load statement of the CURRENT table but beyond that i am stuck.... Please help!

Re: identify missing records using a table expression

awhitfield — Fri, 11 Mar 2016 15:34:08 GMT

Hi Chris,

can you upload a small example qvw that illustrated the problem?

Andy

Re: identify missing records using a table expression

Saravanan_Desingh — Fri, 11 Mar 2016 15:41:59 GMT

Which Field(s) are you using as Dimension(s). It looks like ID and Name are not Unique.

In general, Dimensions will show only the Unique combinations.

Re: identify missing records using a table expression

chrismtb — Fri, 11 Mar 2016 16:33:24 GMT

ID is the only common field, i actually renamed the Name field in the history table as H_Name (just missed of my original post

Re: identify missing records using a table expression

Digvijay_Singh — Fri, 11 Mar 2016 16:52:54 GMT

Your IF statement tells that you want to pick live status record from current and the before live statuses from history for a particular ID. Are you trying to pick the situation when all 1-4 status available in current table? Not able to understand the logic behind your IF statement properly.

Re: identify missing records using a table expression

chrismtb — Fri, 11 Mar 2016 17:57:01 GMT

Digvijay,

I will shortly post an example Qvd with the problem... in answer to your comment i have maybe about 12 statuses in total however I only showed 4 on the sample tables above.

My original IF statement marked records on the following condition:

They currently have a status of 4 or higher
For the history period (date) selected the records had a status of 3 or lower

In doing this though it excludes new records as they do not exist in the history table and therefore have no previous status.

it is just as important for me to capture and display these records as the ones which meet the criteria above.

Hope this explains a little better?

Re: identify missing records using a table expression

Digvijay_Singh — Fri, 11 Mar 2016 18:58:31 GMT

May be you can create mapping table with ID and Hist_status and check that while loading current table to set the flag -

Exist_Map:

Mapping_Load

ID, Hist_Status

Resident HISTORY;

While loading CURRENT table you can check as -

If(Applymap('Exist_Map',ID,'NA')='NA','NotExist') as _NotExist_Flag;

In your IF condition you can show record by checking this flag.

Re: identify missing records using a table expression

chrismtb — Fri, 18 Mar 2016 14:50:20 GMT

Thanks Digvijay,

The one thing i had to watch for was to load the tables in the correct order (i.e. history first).

From here i just had to use some if statements to ensure my dashboard showed records matching both my criteria.

Top Bananas

Thanks

Chris