Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Adding flags to table

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
;

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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;

View solution in original post

4 Replies
swuehl
MVP
MVP

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;

Not applicable
Author

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;

swuehl
MVP
MVP

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;

Not applicable
Author

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