topic Re: if condition in QlikView

if condition

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

Hi all,

i have written if condition in the script , while debugging with 10000 load i am not getting error, but while i am doing full load i am getting error as invalid expression , Can anyon eplease help where i am going wrong

Thanks

Re: if condition

mshailaja — Tue, 04 Apr 2017 07:10:59 GMT

Anyoneplease look into it

Re: if condition

tunoi — Tue, 04 Apr 2017 07:15:32 GMT

try

sum(if(NEW_STATUS='RE',Dur) - sum(if(NEW_STATUS='RE' and WildMatch(NEW_STATUS,'SUPS','OP','CL',), Dur)

Re: if condition

avinashelite — Tue, 04 Apr 2017 07:20:29 GMT

In order to use the sum you need a Group by clause in your table that is missing

Re: if condition

Tue, 04 Apr 2017 07:24:13 GMT

hi,

You can use set analysis instead of IF condition.

If condition has to compare with all the records every time when a script is reloaded,so Qlik will hang.

Always prefer set analysis when any filtering is needed.

syntax will be:

sum({<NEW_STATUS = 'RE'>}Dur)-sum({<NEW_STATUS = {'SUSP*','OP*','CL*'}>}Dur)

Regards,

Balakrishnan.R

Re: if condition

Tue, 04 Apr 2017 07:26:27 GMT

Apologies,

Above logic could be used only in Qlikview objects.

For your question , Follow Avinash suggestion.

Regards,

Balakrishnan.R

Re: if condition

ashok1203 — Tue, 04 Apr 2017 07:52:30 GMT

In Script, If you want use any aggregate function you need to do use the Group By clause.

Re: if condition

mshailaja — Tue, 04 Apr 2017 08:23:32 GMT

i have used Group by in the script, please find the screen shot

while debugging like limied load 10,000 the script is loading successfulyy, but while reloading i am getting the error as 'Invalid expression', please help

Re: if condition

tamilarasu — Tue, 04 Apr 2017 08:33:17 GMT

Hi Sailaja,

Try this,

Group by PERSID, NEW_STATUS, OLD_STATUS;

All non-aggregated fields must be in a group by clause.

Re: if condition

mshailaja — Tue, 04 Apr 2017 08:44:34 GMT

Based on PERSID (common key) i am calculating , if i keep Old_status,new_status, as per my undersatanding i wnt get correct data. can you please help me

Re: if condition

ashok1203 — Tue, 04 Apr 2017 08:48:53 GMT

then try to calculate the same in variables.

Re: if condition

avinashelite — Tue, 04 Apr 2017 08:52:54 GMT

You need to include the PERSID ,NEW_STATUS, OLD_STATUS in the Group by Your forgetting the basic here

group by is a clause used for defining over which fields the data should be aggregated (grouped). The aggregation fields should be included in some way in the expressions loaded. No other fields than the aggregation fields may be used outside aggregation functions in the loaded expressions.

groupbyfieldlist ::= (fieldname { ,fieldname } )

Re: if condition

tamilarasu — Tue, 04 Apr 2017 09:28:44 GMT

Try this,

pre:

Load

Sum(If(NEW_STATUS='RE', Dur)) - Sum(If(WildMatch(NEW_STATUS,'SUPS','OP','CL',), Dur)) as Total_Resolved_Duration,

Sum(If(OLD_STATUS='OP' and NEW_STATUS ='ACK', Dur) as Response Time

Resident GRP

Group by PERSID;

Next time post your script in text format not as picture.

Re: if condition

mshailaja — Tue, 04 Apr 2017 09:55:42 GMT

if i am writing the Group by with

i am getting the duplicate values, how to do

Re: if condition

mshailaja — Tue, 04 Apr 2017 10:16:56 GMT

The incident is repeating as you can see in the screenshot start time and endtime ,old status and new status is repeating twice if am taking Group by PERSID, NEW_STATUS, OLD_STATUS;

Re: if condition

avinashelite — Tue, 04 Apr 2017 10:17:14 GMT

You always share only half of the information , I feel Group by is working fine but Duplication might be because of your join condition or the way you have associated the tables are wrong ..you need to share the complete script and app then only we can debug the issue

Re: if condition

tamilarasu — Tue, 04 Apr 2017 10:20:05 GMT

Did you try my suggestion?

pre:

Load

Sum(If(NEW_STATUS='RE', Dur)) - Sum(If(WildMatch(NEW_STATUS,'SUPS','OP','CL',), Dur)) as Total_Resolved_Duration,

Sum(If(OLD_STATUS='OP' and NEW_STATUS ='ACK', Dur) as Response Time

Resident GRP

Group by PERSID;

Re: if condition

mshailaja — Tue, 04 Apr 2017 11:10:16 GMT

I have 2 tables with first asignment group and seconassignment group with fields as

PERSID i have linked with one more table which is having incident no's

thas the logic i have applied in the datamodel

Re: if condition

avinashelite — Tue, 04 Apr 2017 11:31:29 GMT

remove the REF_NUM column and add PERSID and check the data set once ..share your app