Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
paulyeo11
Valued Contributor II

SQL ApplyMap for Rejected Code display missing value

Hi All

I have a SQL script below working fine :-

Pick(Match(kode_reject, 5, 39), 'reject human error', 'reject machine error') AS DESC_REJECT;

Due to too many error code , i decide to change to mapping ( believe this line cause problem ) :-

ApplyMap('SegmentMap', subfield(kode_reject,' ',1), 'Other') AS DESC_REJECT;

I have the Reject code Table which i can only place after SQL load the raw data into QV ( This also another issue i don't know how to solve ), if i place before loading SQL data to QV , i will get error.

SegmentMap:

MAPPING LOAD * INLINE [

Original, New

5,Human error

39,Machine error

A2,Business Partner

];

when i reload script , it does not show any error code , but the DESC_REJECT field all value display null or missing value "-"

any advise where go wrong on below line :-

ApplyMap('SegmentMap', subfield(kode_reject,' ',1), 'Other') AS DESC_REJECT;

1 Solution

Accepted Solutions

Re: SQL ApplyMap for Rejected Code display missing value

I would put subfield(kode_reject,' ',1) as CheckField into the load-statement to look what it returned. Maybe you need a trim() for kode_reject or the space ' ' isn't a space else a special-char like tab or similar.

- Marcus

6 Replies

Re: SQL ApplyMap for Rejected Code display missing value

I would put subfield(kode_reject,' ',1) as CheckField into the load-statement to look what it returned. Maybe you need a trim() for kode_reject or the space ' ' isn't a space else a special-char like tab or similar.

- Marcus

paulyeo11
Valued Contributor II

Re: SQL ApplyMap for Rejected Code display missing value

Hi Sir

Many thank for your hints.

I never modify the below code :-

ApplyMap('SegmentMap', subfield(trim(kode_reject),' ',1), 'Other') AS DESC_REJECT;

I just move below code to before i load SQL data to QV

SegmentMap:

MAPPING LOAD * INLINE [

Original, New

5,Human error

39,Machine error

A2,Business Partner

];

while reload the code , I get the below error code :-

Table not found

CONCATENATE (tmp)

LOAD

  min(date_time) AS MinDate,

  max(date_time) AS MaxDate

RESIDENT record_produksi_plc

Now my applyMap is working fine. except i get the above error.may i know how to avoid this error ?

if i comment below line of script , error will disappear :-

CONCATENATE (tmp)

LOAD

  min(date_time) AS MinDate,

  max(date_time) AS MaxDate

RESIDENT record_produksi_plc;

But i think above script is good for check for min and max date.

Paul

Re: SQL ApplyMap for Rejected Code display missing value

You should look if you really have these tables "tmp" and "record_produksi_plc". QV is case-sensitiv by field/table-names and it will auto-concatenate tables with the same data-structure so the last created table with this structure didn't exists.

- Marcus

paulyeo11
Valued Contributor II

Re: Re: SQL ApplyMap for Rejected Code display missing value

Hi sir

Thank you for your advise again.

The error code happen only when i move the applyMap table to before QV read SQL data.

i think it is due to when applyMap create the table , it cause QV unable to create SQL table. and it cause the error msg appear.

why i said this ?

when i remove the applyMap script , error will disappear.

How to tell QV not to display error msg ?

Paul

Re: Re: SQL ApplyMap for Rejected Code display missing value

I think it's good that qv showed an error msg then otherwise data aren't valid but you could use errormode-variables to react on certain errors.

Your error msg is caused either the applymap will be applied on a sql select (it belonged to qv and not to the sql-db) or the load-order is wrong. QV executed the statements through all tab-sheets from top to down and from left to right and a mapping-table must be loaded before an applymap is possible.

- Marcus

paulyeo11
Valued Contributor II

Re: SQL ApplyMap for Rejected Code display missing value

Yes , strongly agree with you.

Community Browser