topic Re: SQL script on record label for example 114 human error 113 machine error. in QlikView

SQL script on record label for example 114 human error 113 machine error.

paulyeo11 — Tue, 18 Nov 2014 05:24:03 GMT

Hi All

I like to record reject code, 114 = Reject by human error 113= Reject by machine problem.

My SQL load script as below i get error msg :-

LOAD

if(kode_reject=114,"reject human error")

if(kode_reject=113,"reject machine error") as DESC_REJECT;

select * from record_reject_detail_plc;

Paul

Re: SQL script on record label for example 114 human error 113 machine error.

SergeyMak — Tue, 18 Nov 2014 05:28:28 GMT

Hi Paul,

It would be better

LOAD *,

if(kode_reject=114,'reject human error'),

if(kode_reject=113,'reject machine error')) as DESC_REJECT;

select * from record_reject_detail_plc;

Regards,

Sergey

Re: SQL script on record label for example 114 human error 113 machine error.

SergeyMak — Tue, 18 Nov 2014 05:31:12 GMT

Or you can use

LOAD *,

Pick(Match(kode_reject, 114, 113), 'reject human error', 'reject machine error') AS DESC_REJECT;

select * from record_reject_detail_plc;

Regards,

Sergey

I prefer this option

Re: SQL script on record label for example 114 human error 113 machine error.

Tue, 18 Nov 2014 05:31:47 GMT

Hi,

Use this

LOAD

if(kode_reject=114,'reject human error',if(kode_reject=113,'reject machine error')) as DESC_REJECT;

select * from record_reject_detail_plc;

Regards,

Navdeep Kumar

Re: SQL script on record label for example 114 human error 113 machine error.

albertovarela — Tue, 18 Nov 2014 05:35:16 GMT

Don't forget to add SQL before the select ...... SQL select * from record_reject_detail_plc;

Re: SQL script on record label for example 114 human error 113 machine error.

SergeyMak — Tue, 18 Nov 2014 05:41:23 GMT

You are correct

So it will be

LOAD *,

if(kode_reject=114,'reject human error'),

if(kode_reject=113,'reject machine error')) as DESC_REJECT;

SQL select * from record_reject_detail_plc;

LOAD *,

Pick(Match(kode_reject, 114, 113), 'reject human error', 'reject machine error') AS DESC_REJECT;

SQL select * from record_reject_detail_plc;

Regards,

Sergey

Re: SQL script on record label for example 114 human error 113 machine error.

jagan — Tue, 18 Nov 2014 05:50:44 GMT

Hi Paul,

Try this script

LOAD *,

Pick(WildMatch(200, 114, 113, '*'), 'reject human error', 'reject machine error', 'N/A') AS DESC_REJECT;

SQL select * from record_reject_detail_plc;

Hope this helps you.

Regards,

jagan.

Re: SQL script on record label for example 114 human error 113 machine error.

paulyeo11 — Tue, 18 Nov 2014 05:51:46 GMT

Hi Sergey

Thank , it work fine now :-

LOAD *,

day(date_time) as [day],

year(date_time) as [Year],

Date(Monthstart([date_time]), 'YY MMM') as YearMonth,

if(kode_reject=50,'reject human error',

if(kode_reject=5,'reject machine error')) as DESC_REJECT;

select * from record_reject_detail_plc;

Paul

Re: SQL script on record label for example 114 human error 113 machine error.

paulyeo11 — Tue, 18 Nov 2014 05:54:10 GMT

Hi Jagan

Thank you , i like the N.A portion.

Paul

Re: SQL script on record label for example 114 human error 113 machine error.

paulyeo11 — Tue, 18 Nov 2014 06:12:22 GMT

Hi Jagan

when i try your code again , i notice there are some issue :-

LOAD *,

Pick(WildMatch(200, 114, 113, '*'), 'reject human error', 'reject machine error', 'N/A') AS DESC_REJECT;

SQL select * from record_reject_detail_plc;

why you does not include the Kode_reject name ?

200 does not equal reject human error

114 does not equal reject machine error

can you share with me how your code work ?

Paul

Re: SQL script on record label for example 114 human error 113 machine error.

paulyeo11 — Tue, 18 Nov 2014 06:15:50 GMT

Hi Sergey

Just test found your working :-

LOAD *,

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

SQL select * from record_reject_detail_plc;

Paul

Re: SQL script on record label for example 114 human error 113 machine error.

paulyeo11 — Tue, 18 Nov 2014 06:17:55 GMT

Hi Jagon

the correct one should be :-

LOAD *,

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

SQL select * from record_reject_detail_plc;

Re: SQL script on record label for example 114 human error 113 machine error.

jagan — Tue, 18 Nov 2014 06:23:05 GMT

Hi,

Paul

LOAD *,

Pick(WildMatch(kode_reject, 114, 113, '*'), 'reject human error', 'reject machine error', 'N/A') AS DESC_REJECT;

SQL select * from record_reject_detail_plc;

For testing I put 200, you can replace 200 with your actual field name.

Regards,

jagan.

Re: SQL script on record label for example 114 human error 113 machine error.

SergeyMak — Tue, 18 Nov 2014 06:23:51 GMT

If you need N/A

just use the following:

LOAD *,

Pick(WildMatch(kode_reject, 5, 39,'*'), 'reject human error', 'reject machine error','N/A') AS DESC_REJECT;

SQL select * from record_reject_detail_plc;