topic Adding flags to table in QlikView

Adding flags to table

Wed, 21 Nov 2012 09:24:19 GMT

I have a script where i want to add Yes/No-fields to the main customer table with data from a separate table. The relation to this table is 1 to 0/n.

This script works, but is there perhaps a simpler/better way?

Best regards

Martin

Customers:
LOAD * INLINE 
[
    CustomerId, Name
    1, John
    2, Ann
    3, Max
    4, Peter
    5, Helen
];
Nix:
LOAD * INLINE 
[
    CustomerId, Type
    1, NixEmail
    1, NixTelephone
    3, NixTelephone
    5, NixEmail
];

Temp:
LOAD
   CustomerId
RESIDENT
   Customers
;
 
LEFT JOIN (Temp)
LOAD
   CustomerId,
   'Yes' AS NixEmailTemp 
RESIDENT   
   Nix
WHERE
   Type = 'NixEmail'  
;
   
LEFT JOIN (Temp)
LOAD
   CustomerId,
   'Yes' AS NixTelephoneTemp 
RESIDENT   
   Nix
WHERE
   Type = 'NixTelephone'  
;
 
LEFT JOIN (Customers)
 
LOAD
   CustomerId,
   IF(NixEmailTemp = 'Yes', 'Yes', 'No')     AS NixEmail,
   IF(NixTelephoneTemp = 'Yes', 'Yes', 'No') AS NixTelephone
RESIDENT
    Temp
;
 
DROP TABLES Temp, Nix
;

Re: Adding flags to table

swuehl — Wed, 21 Nov 2012 09:43:14 GMT

Maybe like this, using Mapping instead of Joins:

Customers:

LOAD * INLINE

[

CustomerId, Name

1, John

2, Ann

3, Max

4, Peter

5, Helen

];

Nix:

LOAD *, 'Yes' as Flag INLINE

[

CustomerId, Type

1, NixEmail

1, NixTelephone

3, NixTelephone

5, NixEmail

];

MAP1:

MAPPING LOAD CustomerId, 'Yes' Resident Nix where Type = 'NixEmail';

MAP2:

MAPPING LOAD CustomerId, 'Yes' Resident Nix where Type = 'NixTelephone';

RESULT:

LOAD

CustomerId,

Name,

Applymap('MAP1', CustomerId, 'No') as NixEmail,

Applymap('MAP2', CustomerId, 'No') as NixTelephone

Resident Customers;

drop tables Customers, Nix;

Re: Adding flags to table

Wed, 21 Nov 2012 10:04:34 GMT

Thanks a lot for your input. I never tried Mapping before. Usually do most stuff in the database.

I redid the script a little since i needed the days also since last nix per channel.

So now the code looks a little like this. Is this optimal?

Best regards

Martin

Nix:
LOAD * INLINE 
[
    CustomerId, Type, DateType
    1, NixEmail, 2012-11-21
    1, NixTelephone, 2012-05-22
    3, NixTelephone, 2011-12-12
    5, NixEmail,2011-07-01
];
 
MapNixEmail:
MAPPING LOAD CustomerId, 'Yes' RESIDENT Nix WHERE Type = 'NixEmail';
 
MapNixTelephone:
MAPPING LOAD CustomerId, 'Yes' RESIDENT Nix WHERE Type = 'NixTelephone';
 
MapNixEmailDays:
MAPPING LOAD CustomerId, TODAY(1) - DateType RESIDENT Nix WHERE Type = 'NixEmail';
 
MapNixTelephoneDays:
MAPPING LOAD CustomerId, TODAY(1) - DateType RESIDENT Nix WHERE Type = 'NixTelephone';
 
Customers:
LOAD * INLINE 
[
    CustomerId, Name
    1, John
    2, Ann
    3, Max
    4, Peter
    5, Helen
];
  
LEFT JOIN (Customers)
 
LOAD 
   CustomerId, 
   APPLYMAP('MapNixEmail', CustomerId, 'No')         AS NixEmail, 
   APPLYMAP('MapNixEmailDays', CustomerId, '')       AS NixEmailDays,   
   APPLYMAP('MapNixTelephone', CustomerId, 'No')     AS NixTelephone,
   APPLYMAP('MapNixTelephoneDays', CustomerId, '')   AS NixTelephoneDays   
RESIDENT 
   Customers
;
 
DROP TABLES Nix;

Re: Adding flags to table

swuehl — Wed, 21 Nov 2012 10:14:11 GMT

If I understand your data correctly, you should be able to get rid of the join and do the mapping while loading your customer data:

Customers:

LOAD * ,

APPLYMAP('MapNixEmail', CustomerId, 'No') AS NixEmail,

APPLYMAP('MapNixEmailDays', CustomerId, '') AS NixEmailDays,

APPLYMAP('MapNixTelephone', CustomerId, 'No') AS NixTelephone,

APPLYMAP('MapNixTelephoneDays', CustomerId, '') AS NixTelephoneDays

INLINE

[

CustomerId, Name

1, John

2, Ann

3, Max

4, Peter

5, Helen

];

//LEFT JOIN (Customers)

//LOAD

// CustomerId,

// APPLYMAP('MapNixEmail', CustomerId, 'No') AS NixEmail,

// APPLYMAP('MapNixEmailDays', CustomerId, '') AS NixEmailDays,

// APPLYMAP('MapNixTelephone', CustomerId, 'No') AS NixTelephone,

// APPLYMAP('MapNixTelephoneDays', CustomerId, '') AS NixTelephoneDays

//RESIDENT

// Customers

//;

DROP TABLES Nix;

exit script;

Re: Adding flags to table

Wed, 21 Nov 2012 11:18:47 GMT

Yes, that's what i also figured out. Thanks again.