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

Create a field in the data model that allows selecting excluded values

Hi all,

Hope you can support me with the following:

I have four input tables (from Excel) with the client base and specific flags (Type, Region & Importance).

 

 

Now I am trying to select all the clients, which meet following criteria:

  • Client type is not VIP
  • Client is not from EUROPE
  • Importance is not High

The result should be Company E, Company F and Company J.

As you see, first table contains all clients from the database. But only few of them are included in the other 3 tables. Thus tables 2, 3 and 4 have missing values for some clients.

As I understand possible approach could be to create new fields in the script and add basically a flag.

Something like this:

Table2:
LOAD [Client_ID],
[Type]
FROM
Table2.xlsx
(
ooxml, embedded labels, table is type);

Table1:
LOAD [Client_ID],
Name,
If( Exists([Client_ID]), Dual('VIP', True()), Dual('No', False()) ) as [Client type]
FROM
Table1.xlsx
(
ooxml, embedded labels, table is Base);

But it seems to work only for one table. Do you know, how can I include remaining two tables as well? Shoul I follow a different approach?

Many thanks!

Best Regards

Dimitri

3 Replies
Not applicable

Re: Create a field in the data model that allows selecting excluded values

Try link below with Mapping tables;

MAP_VIP:

Mapping LOAD ClientID, Type From Type.xls;

MAP_Region:

Mapping LOAD ClientID, Region From Region.xls;


MAP_Imp:

Mapping LOAD ClientID, Importance From Importance.xls;


Table:

LOAD

     ClientID ,

     Name,

     ApplyMap('MAP_VIP',ClientID,'Not Vip') AS Type,

     ApplyMap('MAP_Region',ClientID,'Non Europe') AS Region,

     ApplyMap('MAP_Imp',ClientID,'not High') AS Importance

From Source.

MVP
MVP

Re: Create a field in the data model that allows selecting excluded values

1.png

T1:

load

rowno() as Client_ID,

'Company ' & chr(ord('A')-1 + rowno()) as Name

AutoGenerate 10;

T2:

load * inline [

Client_ID,Type

1,VIP

3,VIP

7,VIP

8,VIP

];

tmp: load Client_ID as C Resident T2;

Concatenate (T2)

load Client_ID, 'NOVIP' as Type resident T1 where not exists(C, Client_ID);

DROP Table tmp;

T3:

load * inline [

Client_ID,Region

1,EUROPE

2,EUROPE

4,EUROPE

];

tmp: load Client_ID as C Resident T3;

Concatenate (T3)

load Client_ID, 'NO EUROPE' as Region resident T1 where not exists(C, Client_ID);

DROP Table tmp;

T4:

load * inline [

Client_ID,Importance

3,High

4,High

9,High

];

tmp: load Client_ID as C Resident T4;

Concatenate (T4)

load Client_ID, 'NO High' as Importance resident T1 where not exists(C, Client_ID);

DROP Table tmp;

Arjunarao
Honored Contributor II

Re: Create a field in the data model that allows selecting excluded values

Hi Trymbach

Please attach the excel file? THank you.

Community Browser