Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a column M_TP_TYPO and I want to load it only if the first few characters start with A240. eg it will load A240_123, A240_567 and A240_890 but it will not load values like A123_123 etc.
I am currently using the below statement but there seems to be some null values in my rows being loaded. How do I not include these null rows? Meaning I strictly only want rows which have values A240*.
IF(WILDMATCH(M_TP_TYPO, 'A240*'), M_TP_TYPO) AS TYPOLOGY,
Likewise, I want to load M_TP_RTPR0 if the value is 'P'. But the below code too is giving me null rows. How do I strictly have values with 'P'? please help thank you
IF(M_TP_RTPR0='P', 'P') AS PAY_RECV,
There could be unwanted spaces around the values. Try comparing after removing them using trim() like:
IF(trim(M_TP_RTPR0)='P', 'P') AS PAY_RECV,
Hi,
Try below
IF(M_TP_RTPR0='P', M_TP_RTPR0) AS PAY_RECV,
Regards,
Your condition sould be in where statement.
Load
M_TP_TYPO
, * //some other columns you need
Resident
DataTable
Where
WildMatch(M_TP_TYPO, 'A240*');
Null rows could be because
this is true:
IF(WILDMATCH(M_TP_TYPO, 'A240*'), M_TP_TYPO) AS TYPOLOGY,
But this is not : (So you will get null rows)
IF(M_TP_RTPR0='P', 'P') AS PAY_RECV,
If you want to eliminate the null rows then add a where clause:
Where Match(M_TP_RTPR0,'P');
and this field should be loaded like
M_TP_RTPR0 AS PAY_RECV,
Here you have a load script:
Data:
load * inline [
"M_TP_TYPO", "M_TP_RTPR0"
"A240_123", "P"
"A240_567", "E"
"A240_890", "P"
"A123_123", "E"
];
TYPOLOGY:
load
M_TP_TYPO as TYPOLOGY
Resident
Data
Where WildMatch(M_TP_TYPO, 'A240*');
onlyP:
load
M_TP_RTPR0 as ONLY_P_VALUE
Resident
Data
Where Match(trim(M_TP_RTPR0), 'P');
Hi,
If you want both condition true then try below
IF(WILDMATCH(M_TP_TYPO, 'A240*') and M_TP_RTPR0='P', 'P') AS PAY_RECV
Regards,