Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Dealing with non-exisiting values in one of two tables

Hello community,

I load two tables from a database and save each to a qvd. Both are connect through the ID-column name.

Table Master contains the master data meaning that I only want to deal with the IDs of table B (Slave) where they match with A. For that I used Where Exists(ID, ID) in the slave table.


The slave table contains measure values for each ID. If an ID of the master table is not present in the slave table I get wrong numbers when I do a distinct count to get the numbers for the MeasureType column.

See this screenshot for details: Note: Here I don't use "where exists" to show you the complete example.

measure.PNG.png

The goal here is to deal with the non-existing IDs in the slave table (77, 88) so that "No measure" has a count of 3 instead 1.

Would this work with joining only a subset of the columns from both tables or which way do I have to go here?

I included my sample qvw so you can have a look.

Thank you,

Thorsten

Tags (3)
1 Solution

Accepted Solutions

Re: Dealing with non-exisiting values in one of two tables

Try this:

Master:

LOAD * INLINE [

    ID, Column 1, Column 2, Column 3

    1, (1)col1, (1)col2, (1)col3

    77, (77)col1, (77)col2, (77)col3

    88, (88)col1, (88)col2, (88)col3

];

Slave:

LOAD *

,

if([Measure1] OR [Measure2]  ,'measure1+2',

if([Measure3] OR [Measure3] ,'measure3+4',

if(([Measure5]), 'measure5','NO measure')

)) AS MeasureType,

ID  as ID2

INLINE [

    ID, Measure1, Measure2, Measure3, Measure4, Measure5

    1, 12.34, , , 999.23,

    2,  , , 6.24, 888.79

    3, , 8.37, 6.24, 888.79

    4, , , , , 2

    5, , , , , ,

]   

Where Exists(ID, ID);

Concatenate(Slave)

load ID, 'NO measure' as MeasureType

resident Master

where not exists(ID2,ID);

drop field ID2;


talk is cheap, supply exceeds demand
2 Replies

Re: Dealing with non-exisiting values in one of two tables

Try this:

Master:

LOAD * INLINE [

    ID, Column 1, Column 2, Column 3

    1, (1)col1, (1)col2, (1)col3

    77, (77)col1, (77)col2, (77)col3

    88, (88)col1, (88)col2, (88)col3

];

Slave:

LOAD *

,

if([Measure1] OR [Measure2]  ,'measure1+2',

if([Measure3] OR [Measure3] ,'measure3+4',

if(([Measure5]), 'measure5','NO measure')

)) AS MeasureType,

ID  as ID2

INLINE [

    ID, Measure1, Measure2, Measure3, Measure4, Measure5

    1, 12.34, , , 999.23,

    2,  , , 6.24, 888.79

    3, , 8.37, 6.24, 888.79

    4, , , , , 2

    5, , , , , ,

]   

Where Exists(ID, ID);

Concatenate(Slave)

load ID, 'NO measure' as MeasureType

resident Master

where not exists(ID2,ID);

drop field ID2;


talk is cheap, supply exceeds demand
Not applicable

Re: Dealing with non-exisiting values in one of two tables

Perfect. This does the job. Thank you very much.

Community Browser