Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
;
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;
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;
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;
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;
Yes, that's what i also figured out. Thanks again.