topic Re: SQL ApplyMap for Rejected Code display missing value in QlikView

SQL ApplyMap for Rejected Code display missing value

paulyeo11 — Wed, 19 Nov 2014 09:00:37 GMT

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;

Re: SQL ApplyMap for Rejected Code display missing value

marcus_sommer — Wed, 19 Nov 2014 11:48:00 GMT

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

Re: SQL ApplyMap for Rejected Code display missing value

paulyeo11 — Wed, 19 Nov 2014 12:42:25 GMT

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

marcus_sommer — Wed, 19 Nov 2014 13:01:49 GMT

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

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

paulyeo11 — Wed, 19 Nov 2014 13:16:30 GMT

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

marcus_sommer — Wed, 19 Nov 2014 13:27:59 GMT

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

Re: SQL ApplyMap for Rejected Code display missing value

paulyeo11 — Wed, 19 Nov 2014 13:35:42 GMT

Yes , strongly agree with you.