IF Condition Tricky

[#1]

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

[sunny_talwar]

Try this:

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

Value*percentage)

[Kushal_Chawda]

I think he is asking in script

[tresesco]

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.

[sunny_talwar]

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;

[#6]

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

[#7]

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

[sunny_talwar]

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?

[Colin-Albert]

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.

[#10]

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.