identify missing records using a table expression

[chrismtb]

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!

[awhitfield]

Hi Chris,

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

Andy

[Saravanan_Desingh]

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.

[chrismtb]

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

[Digvijay_Singh]

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.

[chrismtb]

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?

[Digvijay_Singh · Accepted solution]

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.

[chrismtb]

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