topic Re: select exclude problem in QlikView

select exclude problem

Anonymous — Thu, 22 Aug 2013 05:44:24 GMT

hi all,

I'm trying to allow selection in one of my tables. I'm excluding some data, and that process goes well.

the problem is that information from different cells (which brought with set analysis) is missing.

as far as I've noticed, it doesn't happen on a regular filtering (just when i switch the filter to excluded).

that's my selection which i exclude later =if(vComission=1,'708*',''), how can i drop the cards that begins with 708 without using exclusion? or should i change the script to the table? that's the type of the script:

if(Date>=Today(),Sum ({$<TranType={'payment'}>}TranAmtCurr)

the table shows bank currency transactions

before

after

thanks for any help

Re: select exclude problem

tresB — Thu, 22 Aug 2013 05:52:01 GMT

try this :

if(Date>=Today(),Sum ({$<TranType={'payment'}, Card - = {"708*"}>}TranAmtCurr)

Assuming 'Card' is a field.

Re: select exclude problem

jagan — Thu, 22 Aug 2013 06:18:41 GMT

Hi,

you can try like this using expression

=Sum ({$<Date={'>=$(=Today())'}TranType={'payment'}, Card - = {"708*"}>} TranAmtCurr)

or you can arrive a new field in script like this for card

LOAD

If(Left(Card, 3) = '708', 1, 0) AS Is708

FROM DataSource;

then in expression you do like this

=Sum ({$<Date={'>=$(=Today())'}TranType={'payment'},Is708={1} >} TranAmtCurr)

Hope this helps you.

Regards,

Jagan.

Re: select exclude problem

Anonymous — Thu, 22 Aug 2013 08:28:57 GMT

thanks for the quick replies,

"card" is indeed a field, i added a new field (as you noted) in the script but the expression you've suggested didn't work.

i use that expression: if(Date>=Today() and Is708='1',Sum ({$<TranType={'payment'}>}TranAmtCurr))

it works only where the only card begins with 708, if there are few cards, i don't get resoults

Re: select exclude problem

kaushiknsolanki — Thu, 22 Aug 2013 08:34:58 GMT

Hi,

If you want to exclude the Card starting with 708 then in the solution said by Jagan, use the 0 instead of 1.

So your expression will be.

if(Date>=Today() and Is708='0',Sum ({$<TranType={'payment'}>}TranAmtCurr))

This will how the data (Sum of TranAmtCurr) only where the Card doesnt start woth 708.

Regards,

Kaushik Solanki

Re: select exclude problem

Anonymous — Thu, 22 Aug 2013 08:48:24 GMT

hi,

i tried that, but in sections where there are few types of card, the exclude doesn't work .

it works only where the only card begins with 708, if there are few cards, i don't get resoults

Re: select exclude problem

kaushiknsolanki — Thu, 22 Aug 2013 08:54:44 GMT

Hi,

Is it possible for you to put the example file and explain it.

Regards,

Kaushik Solanki

Re: select exclude problem

Anonymous — Thu, 22 Aug 2013 09:46:14 GMT

Hi Kaushik,

sorry but it's a bit difficult for me to create an example file, as some of the data is in Hebrew. but, I'll try to demonstrate my point.

in table A you can see the payment in numerous cards

table B shows the wrong result, as in column "new payment" should be amount of 4,850

thanks for your help

Re: select exclude problem

Anonymous — Sun, 25 Aug 2013 09:27:08 GMT

Hi all,

After some adjustments the correct script is:

if(Date>=Today() ,Sum ({$<TranType={'payment'},Is708={'0'}>}TranAmtCurr))

I put the exclusion field as part of the set analysis

of course after creating a field in the script, as Jagan has suggested

thanks again