Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

paulyeo11
Valued Contributor II

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

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

1 Solution

Accepted Solutions
MVP
MVP

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

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.



13 Replies
sergey_maka8
Valued Contributor

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

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

sergey_maka8
Valued Contributor

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

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

Not applicable

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

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

AlbertoV
Contributor III

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

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

sergey_maka8
Valued Contributor

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

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;

or

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

MVP
MVP

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

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.



paulyeo11
Valued Contributor II

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

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

paulyeo11
Valued Contributor II

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

Hi Jagan

Thank you , i like the N.A portion.

Paul

paulyeo11
Valued Contributor II

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

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

Community Browser