Prevent fields from loading

[mikel_de]

Hello!

Below is a sample of my data and I wonder whether it is possible to use a LOAD expression that loads only the data associated to CountryA. I know that I can also use a filter, but I would prefer to load only the data I need from the beginning.

Country	Customer	Turnover
CountryA	AA	45968
CountryA	AB	34589
CountryA	AC	39458
CountryB	BA	85393
CountryB	BB	53749
CountryC	CA	54522
CountryC	CB	23865
CountryC	CC	23654
CountryD	DA	54233
CountryD	DB	45633

It would be also interesting to know, if I can exclude a particular country, e.g. load everything but CountryC. Thank you very much!

-Mikel

[sunny_talwar · Accepted solution]

May be like this:

LOAD Country,

          Customer,

          Turnover

FROM ....

Where Country = 'CountryA';

For excluding, you can try this:

LOAD Country,

          Customer,

          Turnover

FROM ....

Where Country <> 'CountryC';

Or you can use a Where Exists.

IncludeCountryTable:

LOAD * Inline [

Country

CountryA

];

LOAD Country,

          Customer,

          Turnover

FROM ....

Where Exists(Country);

This will only include CountryA because before this table Load, only CountryA was included in Country field.

Alternatively, to exclude, you can try this:

ExcludeCountryTable:

LOAD * Inline [

Country

CountryC

];

LOAD Country,

          Customer,

          Turnover

FROM ....

Where NOT Exists(Country);

[mikel_de]

Hello and thank you very much for your response! I initially had some trouble because I did it like this:

LOAD

...

FROM ...

Where [Country] = 'CountryA'

(ooxml, embedded labels, table is [2016]);

instead of:

LOAD

...

FROM ...

(ooxml, embedded labels, table is [2016])

Where [Country] = 'CountryA';

Just in case somebody else needs this.

Thanks again and have a nice weekend!

[mikel_de]

I'm sorry, one more question - what expression should I use in order to aggregate a couple of countries into a region. Let's say I want to display CountryB+CountryC+CountryD as Europe_East.

[sunny_talwar]

I would say something like this:

LOAD If(Match(Country, 'CountryB', 'CountryC', 'CountryD'), 'Europe_East',

          If(...... )) as Region,

          Country,

          Customer,

          Turnover

FROM .....;

[mikel_de]

Thank you, but I fail to do it correctly. I do not understand the second if as well. What I need is to aggregate A, B, and C into Europe_East so i did it like this, but it is obviously wrong. 😕

LOAD

    If(Match(Country, 'CountryB', 'CountryC', 'CountryD')) as Europe_East,

    [Country],

    [Customer],

    [Turnover]

FROM ...

[sunny_talwar]

Is this the requirement on the front end...

I used this script:

Table:

LOAD If(Match(Country, 'CountryB', 'CountryC', 'CountryD'), 'Europe_East', 'Europe_West') as Region,

  Country,

     Customer,

     Turnover

FROM

[https://community.qlik.com/thread/225345]

(html, codepage is 1252, embedded labels, table is @1);

[mikel_de]

Okay, now it works, but I only want to aggregate CountryB, CountryC, and CountryD into Europe_East.

CountryA and the rest (I also have E, F, and so on) should remain as they are. Is this possible?

----

Edit: Never mind, found a way:

If(Match(Country, 'CountryB', 'CountryC', 'CountryD'), 'Europe_East', [Country]) as Region,

Thank you again!