selecting non existing data

[#1]

I have a country table (Code, Description):

US, United States

UK, United Kingdom

DE, Germany

BE, Belgium

I also have a Customer table (Code, Description, CountryCode):

1, John D., US

2, Jane D., UK

3, Jan K.,

4, Sjef Z., BE

5, Piet S.,

6, Dieter M., DE

Customers 3 and 5 don't have a CountryCode. Within the database this means that the country is Netherlands.

I have 2 Table Boxes in QlikView, one for each table.

I want to select all customers that are from The Netherlands.

How do I do that in QlikView?

[jvitantonio · Accepted solution]

Add a list box with the CountryCode field and select "-"

If you want to do it directly in the table, you need to use a pivot table.

[Michiel_QV_Fan]

This is a nice solution from qlikuser, however, you want as little as possible transformations in your dimensions. Prefereable in your script.

Try this also:

1. Load your country table
2. Add NL with a concatenation
3. Load your customers
4. Replace empty values with an If statement

For testing purposes I did some inline tables so you see a resident load to be able to perform the If statement. Drop and noconcatenate to load customers.

countries:

LOAD * INLINE [

    CountryCode, Country

    US, USA

    UK, U.K.

    DE, Germany

    BE, Belgium

];

Concatenate load * inline [

CountryCode, Country

NL, Nederland

];

customers_temp:

LOAD * INLINE [

    Code, Description, CountryCode

    1, John, US

    2, Jane, UK,

    3, Jan, ,

    4, Sjef, BE

    5, Dieter, DE

];

customers:

NoConcatenate load

    Code,

    Description,

    if(len(trim(CountryCode)) >0 , CountryCode, 'NL') as CountryCode   

Resident customers_temp;

drop table customers_temp;