topic Re: IF Condition Tricky in QlikView

IF Condition Tricky

Wed, 25 Nov 2020 16:16:04 GMT

Hi All

I need to multiply Percentage of different Product with another Product in Nested IF condition

and that too in Back end Script.

Kindly go through my Attachment.

Regards

Eric

Re: IF Condition Tricky

sunny_talwar — Wed, 13 Apr 2016 09:50:53 GMT

Try this:

If(Product='D',Value*only(total {<Product={'C'}>}percentage),

Value*percentage)

Re: IF Condition Tricky

Kushal_Chawda — Wed, 13 Apr 2016 09:56:42 GMT

I think he is asking in script

Re: IF Condition Tricky

tresB — Wed, 13 Apr 2016 10:05:26 GMT

LOAD *,

If(Product='D',Value*Previous(percentage), Value*percentage) AS Amount

INLINE [

Proposal, Product, percentage, Value

1111, A, 5, 1000

1112, B, 10, 2000

1113, C, 20, 3000

1114, D, 30, 4000

];

Note: Here Load Order of data matters. That is, you might have to user Order By clause while loading the data.

Re: IF Condition Tricky

sunny_talwar — Wed, 13 Apr 2016 10:07:24 GMT

May be this in the script:

Table:

LOAD * INLINE [

Proposal, Product, percentage, Value

1111, A, 5, 1000

1112, B, 10, 2000

1113, C, 20, 3000

1114, D, 30, 4000

];

Left Join (Table)

LOAD percentage as NewPercentage

Resident Table

Where Product = 'C';

FinalTable:

LOAD Proposal,

Product,

percentage,

Value,

Value*If(Product = 'D', NewPercentage, percentage) AS Amount

Resident Table;

DROP Table Table;

Re: IF Condition Tricky

Wed, 13 Apr 2016 17:50:47 GMT

Hi Sunny,

Thanks for the Reply.

I need one line Code if possible.

I have one question??

You have done Left Join without any Primary Key...... Is this right approach........!!

Regards

Eric

Re: IF Condition Tricky

Wed, 13 Apr 2016 17:52:58 GMT

Dear Tresesco

Thanks for the Reply.

I have just shared the sample data. There are multiple field values against them there are different-different Percentages.

Also, Peek and Previous will not do the trick in my case.

Is there any other alternative.

Regards

Eric

Re: IF Condition Tricky

sunny_talwar — Wed, 13 Apr 2016 17:55:41 GMT

What is an issue with joining a single value to multiple rows? I don't think we need a primary key here, infact this won't work if we add a primary key.

One line code? Do you have something in mind? Have you checked out tresesco's solution may be?

Re: IF Condition Tricky

Colin-Albert — Wed, 13 Apr 2016 18:07:49 GMT

QlikView join syntax does not require any key fields to be defined.

Any identical field names are matched, any different field names are added as new columns.

Re: IF Condition Tricky

Wed, 13 Apr 2016 18:39:14 GMT

One Line Code I mean small and Powerful.

Like something i tried in Script but failed. See this:

IF(Product='D',Value*(IF(Product='C',Percentage)),Value*Percentage

I think what i am trying is a wrong approach.

I don't want to take a long route.

Re: IF Condition Tricky

Wed, 13 Apr 2016 19:08:22 GMT

Thank You Colin and Sunny

I am here for my issue and now i have learned some really great concept which i was not knowing earlier.

Thank You again.

Re: IF Condition Tricky

Wed, 13 Apr 2016 19:10:26 GMT

I have one question??

Does that thing cause duplicate values and multiple frequencies against one unique ID.