Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Become an analytics expert with Qlik's new 15 week course: Applied Data Analytics using Qlik Sense. READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Load columns that match first few characters

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,

6 Replies
tresesco
MVP
MVP

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,

Great dreamer's dreams never fulfilled, they are always transcended.
mfchmielowski
Creator II
Creator II

Your condition sould be in where statement.

Load

     M_TP_TYPO

     , * //some other columns you need

Resident

     DataTable

Where

     WildMatch(M_TP_TYPO, 'A240*');

qlikdeez
Specialist
Specialist

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
Creator II
Creator II

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,

Great dreamer's dreams never fulfilled, they are always transcended.