topic Re: If or where in a chart expression in QlikView

If or where in a chart expression

Wed, 14 Sep 2016 20:22:36 GMT

If([Pledge Campaign Year]<[Gift Campaign Year], sum([Gift Amount]), Null())

Trying to use this code in a chart expression to sum only the gifts that have a campaign year < pledge campaign year but it isn't working.

Re: If or where in a chart expression

swuehl — Wed, 14 Sep 2016 20:58:57 GMT

Try

sum(If([Pledge Campaign Year]<[Gift Campaign Year], Gift Amount]))

Re: If or where in a chart expression

sunny_talwar — Wed, 14 Sep 2016 23:46:49 GMT

You might also be able to use set analysis if you are able to figure out a dimension which uniquely identifies each and every row in your script or create one.

LOAD RowNo() as Key,

[Pledge Campaign Year],

[Gift Campaign Year],

[Gift Amount]

FROM ...

and then use this:

Sum({<Key = {"=[Pledge Campaign Year] < [Gift Campaign Year]"}>} [Gift Amount])

Re: If or where in a chart expression

swuehl — Thu, 15 Sep 2016 05:54:28 GMT

Sunny, what would be the benefit in this case using set analysis, if you apply the advanced search upon a hypercube built with a dimension on row granularity?

I don't think it's advantageous to apply set analysis just to use it.

Just my 0.02 €

Re: If or where in a chart expression

sunny_talwar — Thu, 15 Sep 2016 09:33:11 GMT

How about something like this:

LOAD AutoNumber([Pledge Campaign Year]&[Gift Campaign Year]) as Key,

[Pledge Campaign Year],

[Gift Campaign Year],

[Gift Amount]

FROM ...

and then use this:

Sum({<Key = {"=[Pledge Campaign Year] < [Gift Campaign Year]"}>} [Gift Amount])

Would this be a better solution you think?

Re: If or where in a chart expression

swuehl — Thu, 15 Sep 2016 10:45:49 GMT

It will depend on the possible combinations of the year field.

Probably better than doing a per row comparison, right.

What about

LOAD If([Pledge Campaign Year] < [Gift Campaign Year],1,0) as Flag,

[Pledge Campaign Year],

[Gift Campaign Year],

[Gift Amount]

FROM ...

and then use this:

Sum({<Flag = {1} >} [Gift Amount])

Sum(Flag*[Gift Amount])

My comment was more about using set analysis with care, we are often tempted to use it as often as possible...

Regards,

Stefan

Re: If or where in a chart expression

sunny_talwar — Thu, 15 Sep 2016 10:47:51 GMT

No, I think you are right. I have never thought about it in such a way. Thanks for sending me in that direction . I think Flag option makes much more sense

Re: If or where in a chart expression

Tue, 20 Sep 2016 17:48:39 GMT

The problem that I am having is that I have a table linked to an alias of itself.

GiftTable

GiftTable2

where gifttable.pledgekey = gifttable2.giftkey

and gifttable.campaignyr > gifttable2.campaignyr.

So when I try this join it doesn't recognize the field.

Field not found - <GIFTTable1 Campaign Year>

LEFT JOIN (GiftTable) LOAD

[Gifttable2 PledgeKey] AS [Gift Pledge Key],

[GiftTable2 Campaign Year] as [Gifttable2 Campaign Year],

[Gifttable2 GiftKey]

RESIDENT Gifttable2

WHere [GiftTable2 Amount Pledged] < '25000'

and [GiftTable2 Campaign Year] < [GIFTTable Campaign Year]

Re: If or where in a chart expression

swuehl — Tue, 20 Sep 2016 18:26:58 GMT

Where in your script are you using "GIFTTable1 Campaign Year"?

Re: If or where in a chart expression

Tue, 20 Sep 2016 18:29:41 GMT

Sorry should be gifttable not gifttable1

Re: If or where in a chart expression

swuehl — Tue, 20 Sep 2016 18:46:40 GMT

Well, I assume you can only filter your records using both tables after the JOIN has happened. So first join the two tables, then do a RESIDENT LOAD with the WHERE clause.

Re: If or where in a chart expression

Fri, 23 Sep 2016 15:24:57 GMT

I am a newbie. So I do the join without the where then how do I do the resident load?

Re: If or where in a chart expression

swuehl — Fri, 23 Sep 2016 21:28:33 GMT

Just like the other resident loads in your script.

Something along this snippet:

// load first table

Table1:

LOAD Key,

Field1

FROM YourTable1Source;

// join second to first

LEFT JOIN (Table1)

LOAD

Key,

Field2

FROM Yourtable2Source;

Result:

NOCONCATENATE // prevent QV from autoconcatenating

LOAD Key, Field1, Field2

RESIDENT Table1

WHERE Field1 < Field2; // use your filter

DROP TABLE Table1;