Adding flags to table

[#1]

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
;

[swuehl · Accepted solution]

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;

[#3]

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]

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;

[#5]

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