Qlik Logic - Standard Table To Associate with Dataset to find out missing Work IDs

[rammuthiah]

Table : Stage

Work ID

G1-001

G1-002

G1-003

G1-004

G1-005

G1-006

G1-007

G1-008

G1-009

G1-010

G2-001

G2-002

G2-003

G2-004

G2-005

G2-006

G2-007

G2-008

G2-009

G2-010

Table 2

Dataset
Project	Gate	Work ID
1	Gate 1	G1-001
1	Gate 1	G1-002
1	Gate 1
1	Gate 1	G1-004
1	Gate 1	G1-005
1	Gate 2
1	Gate 2
1	Gate 2
1	Gate 2	G2-004
1	Gate 2	G2-005
2	Gate 1
2	Gate 1	G1-002
2	Gate 1	G1-003
2	Gate 1	G1-004
2	Gate 1
2	Gate 2	G2-001
2	Gate 2	G2-002
2	Gate 2
2	Gate 2	G2-004
2	Gate 2
2	Gate 2	G2-006

Output : Projects numbers are multiple and the above is just a sample numbers, we have n number of projects.

Output
Project	Missing Gate
1	G1-003
1	G1-006
1	G1-007
1	G1-008
1	G1-009
1	G1-010
1	G2-001
1	G2-002
1	G2-003
1	G2-006
1	G2-007
1	G2-008
1	G2-009
1	G2-010
2	G1-001
2	G1-005
2	G1-006
2	G1-007
2	G1-008
2	G1-009
2	G1-010
2	G2-003
2	G2-005
2	G2-007
2	G2-008
2	G2-009
2	G2-010

[marcus_sommer]

A direct association between missing keys isn't possible but you may apply something like this:

t: load ID, 'exists' as Flag from PartialDataset;
concatenate(t) load ID, 'missing' as Flag from FullDataset where not exists(ID);

[rammuthiah]

Could you please explain as for above example as I clready have ProjectNumber as Flag?

[marcus_sommer]

A quite common scenario which is based for my shown example is loading the facts and adding to them per concatenation (respectively union in sql) the missing products/projects/dates/whatever ... from the relevant dimenson-tables. Only the added key-value is real and all other field-values are set to sensible defaults, like ZERO's or ''missing' or null() and of course you will also add one or more flag/source-fields to be able to differentiate between native data and the populated ones.

[rammuthiah · Accepted solution]

Hi,

I joined dataset project number to get all workid to associate with pthatroject. then i load again dataset to associate with stages file. and marked a flag for the missing workid. that way it works.