topic selecting non existing data in QlikView

selecting non existing data

Wed, 14 Mar 2012 13:29:57 GMT

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?

Re: selecting non existing data

jvitantonio — Wed, 14 Mar 2012 13:36:32 GMT

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.

selecting non existing data

Michiel_QV_Fan — Wed, 14 Mar 2012 14:11:05 GMT

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:

Load your country table
Add NL with a concatenation
Load your customers
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;