topic Re: What's wrong in this expression in the script? Applymap & IF statement in QlikView

What's wrong in this expression in the script? Applymap & IF statement

Mattia — Sat, 16 Nov 2024 02:06:52 GMT

Hi guys,

attached is a sample data file (is a personal/desktop) edition.

The output i want to obtain is the following:

if field3 = 1, the first 5 number of field4 and the last 2 of the same field.

field3 is not in table, so i use applymap function.

Can someone help me?

Thanks,

Mattia

tresB — Fri, 25 Oct 2019 09:21:17 GMT

Is this your expected output?

Mattia — Fri, 25 Oct 2019 09:27:52 GMT

Hi Tresesco,

thanks for the message.

No, only in record with field2 01700000, o1600000 and ps000000 i want to see the value in new_field.

Because only in these record field3 = 1.

tresB — Fri, 25 Oct 2019 09:33:56 GMT

Try this:

If(ApplyMap('KEY_TO_FIELD3',[FIELD2],'n/a')=1, (Left([FIELD4],5) & ',' & Right([FIELD4],2))) as NEW_FIELD;

Mattia — Fri, 25 Oct 2019 09:45:35 GMT

Yes, i've tried this expression and it works.

But, field3 in my file has been imported from a txt file.

The script is something like: [@18:20] as field3 and in the straight table i not see "1" but "001".

I think it probably be a problem of format but i don't know how to solve it!

tresB — Fri, 25 Oct 2019 10:00:56 GMT

001 is a text value here. You could get 1 from it using num(). Remove comma and try like:

If(ApplyMap('KEY_TO_FIELD3',[FIELD2],'n/a')=1, Num(Left([FIELD4],5) & Right([FIELD4],2))) as NEW_FIELD;

Mattia — Fri, 25 Oct 2019 10:10:15 GMT

Sorry, but i don't understand the solution.

Now, in my file, new_field is correctly calculated.

The thing that is wrong is that the sw probably don't recognize that i want to see this value only in the records that have field3 = 1 !