Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Try this:
If(Product='D',Value*only(total {<Product={'C'}>}percentage),
Value*percentage)
I think he is asking in script
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.
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;
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
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
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?
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.
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.