topic Re: Straight table if() statements in QlikView

Straight table if() statements

Fri, 21 Mar 2014 12:24:03 GMT

Hello all, this is my first post and I am fairly new to QlikView so please bear with me.

I have a set of information illustrated by the table below, I have labelled them A, B & Boolean so hopefully the answer will apply to more people than just my specific case.

Field A	Field B	Boolean
A1	B1	1
A1	B2	0
A1	B3	0
A1	B4	1
A2	B1	1
etc	etc	etc

In my application though, Field A is basically a project number, Field B is a project phase and the boolean is whether or not that phase is complete.

Basically what I need is to be able to say 'If B2 is not complete AND B1 is complete then B2 is due' So that the output would look something like:

Field A	Field B	Is it Due
A1	B2	Due
etc	etc	etc

I thought something like:

=if(FieldB='B2' AND Boolean = 0, if(FieldA='B1' AND Boolean = 1, 'Due', 'Not Due'))

But I think I'm on completely the wrong track, do I need to load the information differently?

Hopefully this is clear enough, and I'm not asking too stupid a question

Re: Straight table if() statements

its_anandrjs — Fri, 21 Mar 2014 12:36:54 GMT

Hi,

As i understand might you have looking for this please check code

LOAD * Inline

[

FieldA,FieldB,Boolean

A1, B1, 1

A1, B2, 0

A1, B3, 0

A1, B4, 1

A2, B1, 1

etc, etc, etc

];

load

FieldA,FieldB,Boolean,

if(Boolean = 0,'Due','Not Due') as Flag

Resident T;

DROP Table T;

Hope this helps

Thanks & Regards

Re: Straight table if() statements

Fri, 21 Mar 2014 12:44:11 GMT

Hi Anand,

Thanks a lot for responding,

Not quite, If a project phase is not complete it is not necessarily 'Due' because of dependencies. The important part is that, if the phase before it is complete then it is due so in this example below:

Field A	Field B	Boolean
A1	B1	1
A1	B2	0
A1	B3	0
A1	B4	1
A2	B1	1

In project 'A1' phase 'B2' is Due, but 'B3' is not.

Re: Straight table if() statements

its_anandrjs — Fri, 21 Mar 2014 12:48:12 GMT

Hi,

Ok but how you identify the B3 is not Due and what is the use of Boolean field.

Hope this helps

Thanks & Regards

Re: Straight table if() statements

Fri, 21 Mar 2014 12:55:56 GMT

Well exactly, that is what I am asking. Perhaps I was not clear enough. Is there an expression I can write for the given table to add a column saying 'If the row above is complete and this row is not, then it is due'

The boolean says whether a row is complete or not.

Sorry if it is not a very good question

Re: Straight table if() statements

its_anandrjs — Fri, 21 Mar 2014 13:01:43 GMT

Hi,

Yes you are right if any field is created with check the project is completed or not then you are able to display that in application is there any other field.

Hope this helps

Thanks & Regards

Re: Straight table if() statements

Fri, 21 Mar 2014 14:09:05 GMT

I think there has been some mis-communication, thank you for your help anyway

Re: Straight table if() statements

its_anandrjs — Fri, 21 Mar 2014 14:16:41 GMT

Hi,

I think there is some reference field for that.

Hope this helps

Thanks & Regards

Re: Straight table if() statements

PrashantSangle — Fri, 21 Mar 2014 14:32:53 GMT

Hi,

Just Try this in your script,

If(FieldB='B2' AND Boolean=0,'Due') as [Is it Due]

Regards,

Re: Straight table if() statements

Fri, 21 Mar 2014 15:40:44 GMT

Hi Maxdreamer,

Thanks for your response, unfortunately this is not what I am after,

"If a project phase is not complete it is not necessarily 'Due' because of dependencies. The important part is that, if the phase before it is complete then it is due"

It appears what you suggest would just say Due if the Boolean is 0

Regards

Re: Straight table if() statements

PrashantSangle — Fri, 21 Mar 2014 16:03:05 GMT

Hi,

I am no sure that i am understand clearly but as you say,

"If B2 is not complete AND B1 is complete then B2 is due"

I means you are evaluating value for FieldB only because there is no B2 in fieldA and You have to compare with your previous field only

Then use previous() in your script like,

if(FieldB='B2' AND Boolean = 0, if(Previous(FieldB)='B1' AND Previous(Boolean) = 1, 'Due', 'Not Due'),'Not Due') as [Is it Due],

Hope this will help you,

Regards,

Re: Straight table if() statements

menta — Fri, 21 Mar 2014 16:13:17 GMT

You can try something like this

LOAD * Inline

[

FieldA,FieldB,Boolean

A1, B1, 1

A1, B2, 0

A1, B3, 0

A1, B4, 1

A2, B1, 1

etc, etc, etc

]

;

load

FieldA,

FieldB,

Boolean,

if(Peek(FieldA)=FieldA, if(Peek(Boolean)=1 and Boolean=0,'Due','Not due') ) as FlagNew,

if(Boolean = 0,'Due','Not Due') as Flag

Resident T;

DROP Table T;

Re: Straight table if() statements

Fri, 21 Mar 2014 16:28:29 GMT

I think something like this should resolve your question:-

aggr(if(Boolean=0,'Due','Complete'),FieldB,FieldA)

Re: Straight table if() statements

SunilChauhan — Fri, 21 Mar 2014 16:37:51 GMT

may be you looking this

=if(FieldB='B2' AND Boolean = 0,'Due', if(FieldA='B1' AND Boolean = 1,'Due' , 'Not Due'))

hope this helps

Re: Straight table if() statements

Fri, 21 Mar 2014 16:51:27 GMT

Just looking at the documentation for aggr() and I think this is probably where I should be looking. So thanks for this!

The example you posted isn't quite right but it definitely seems like a step in the right direction

I will look into this further, if it solves it I will let you know

Re: Straight table if() statements

Fri, 21 Mar 2014 16:56:15 GMT

This seems to be evaluating based on Field A being B1 at some stage. This would never be the case. Perhaps seeing the data like this might help:

Proj #	Phase	Status
1001	PAY1	1
1001	PAY2	0
1001	PAY3	0
1002	PAY1	1
1002	PAY2	1

When PAY1 is complete for a project, PAY2 is due. But PAY3 is not due until PAY2 is complete.

So I want to add a column using an expression to get the below effect:

Proj #	Phase	Status	Due
1001	PAY1	1	-
1001	PAY2	0	Due
1001	PAY3	0	-
1002	PAY1	1	-
1002	PAY2	1	-

Re: Straight table if() statements

Fri, 21 Mar 2014 17:35:41 GMT

I seem to be nearly there, using the aggr() function using the above field names I have the following:

aggr(if(Boolean = 0 AND above(Boolean) = 1, 'Due', '-')FieldA, FieldB)

I'm going to mark this as correct, I have some issues with my data now but I think they are unique to my data so irrelevant to the community

Thanks for everyone's help big or small

Re: Straight table if() statements

rwunderlich — Fri, 21 Mar 2014 17:51:12 GMT

I'm not sure why you need the aggr(). If you make the dimensions FieldA & FieldB then:

=if(NOT Boolean AND Above(Boolean), 'Due', '')

-ob

Re: Straight table if() statements

Mon, 24 Mar 2014 08:12:40 GMT

Yes of course, how stupid of me. While looking into aggr() I found above(), and just used it in what I already had. Rather than rethinking it!!

Thanks!