topic Re: Cannot use expression result in another expression in QlikView

Cannot use expression result in another expression

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hello,

i'm starting with Qlikview and i find myself stuck in an expression;

i have a pivot table with this expression that i labeled "Delta":

if(Avanzamento='C',8,
if(Avanzamento='D',7,
if(Avanzamento='F',7,
if(Avanzamento='G',6,
if(Avanzamento='H',5,
if(Avanzamento='I',5,
if(Avanzamento='L',5,
if(Avanzamento='M',2,
if(Avanzamento='N',2,
if(Avanzamento='O',2,
if(Avanzamento='S',1)))))))))))

that seems working ok; i then want another expression using the result of this one:

=if((Today() + Delta>>[Scheduled Date]),'Late')

(the idea is to add X days -delta- to today and check the result against the [Scheduled Date] table).
I get an error using the label Delta in any expression, though. I tried substituting the label with the proper column (column(3) in this case) and the expression gives it an ok, but the result is wrong.

Thanks in advance if anyone can help.

Re: Cannot use expression result in another expression

jaibau1993 — Tue, 30 Oct 2018 15:49:57 GMT

Hi!

First of all, are you sure you want to use the '>>' operator? it is a bit operator and it does not mean "greater":

https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/Scripting/Operators/BitOperators.htm

On the other hand, your expression should work. Your problem may be caused because you labeled your Delta with a blank space. I mean, maybe you labeled it as "Delta ". Please, check it.

If that do not solve your problem, can you share a sample?

Bests,

Jaime.

Re: Cannot use expression result in another expression

vitaliichupryna — Tue, 30 Oct 2018 15:59:41 GMT

Hi Erik,

In this situation I would start from the script. Instead of:

if(Avanzamento='C',8,

if(Avanzamento='D',7,

if(Avanzamento='F',7,

if(Avanzamento='G',6,

if(Avanzamento='H',5,

if(Avanzamento='I',5,

if(Avanzamento='L',5,

if(Avanzamento='M',2,

if(Avanzamento='N',2,

if(Avanzamento='O',2,

if(Avanzamento='S',1)))))))))))

You can create Mapping table:

Map:

Mapping Load * Inline[

Avanzamento, Delta

C,8

D,7

F,7

G,6

H, 5

I, 5

L, 5

M, 2

N, 2

O, 2

S, 1

];

Then apply this map on you field:

Load *,

if((Today() + Delta>[Scheduled Date]),'Late') AS Status;

Load

…,

applymap('Map', Avanzamento) AS Delta,

[Scheduled Date]

From …;

After this you have Status field in you table and you will be able to use it as a dimension field

Thanks,

Vitalii

Re: Cannot use expression result in another expression

Anonymous — Wed, 31 Oct 2018 14:41:23 GMT

Hello,

thank you.

Yes, i did want "greater" and was using the wrong operator.
I checked and the label had no blank spaces before or after.
Substituting ">>" with ">" still gives me the "error in expression" message, but the results appear to be right at a glance.

When i get back to the office i'll try with fresh data and go from there.

Re: Cannot use expression result in another expression

Anonymous — Wed, 31 Oct 2018 14:44:35 GMT

Hello,

thank you for taking the time to answer.

I'll try this as an alternative (i'll need a bit to learn about mapping tables, i don't want to just copy&paste ) and seee how this goes.