Load columns that match first few characters

[#1]

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,

[tresesco]

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,

[PrashantSangle]

Hi,

Try below

IF(M_TP_RTPR0='P', M_TP_RTPR0) AS PAY_RECV,

Regards,

[mfchmielowski]

Your condition sould be in where statement.

Load

     M_TP_TYPO

     , * //some other columns you need

Resident

     DataTable

Where

     WildMatch(M_TP_TYPO, 'A240*');

[Anonymous]

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,

[mfchmielowski]

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');

[PrashantSangle]

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,