Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
hi Qv,
Temp:
LOAD Name,
Date,
Year(Date) as Year,
Sales
FROM
Data.xlsx
(ooxml, embedded labels, table is [Dummy Sales]);
Temp1:
NoConcatenate
LOAD
*
Resident Temp
Order by Name, Date;
DROP Table Temp;
CumulativeSum:
NoConcatenate
LOAD
Name,
Date,
Year,
Sales,
If(Name <> Previous(Name) or Year <> Previous(Year), Sales, RangeSum(Sales, Peek('CumulativeSum'))) AS CumulativeSum
Resident Temp1;
DROP Table Temp1;
---------------------------------------------------------------------------------
i have done Cumulative sum for above and the problem is i need commission for Different names commision may vary how to give dynamic in set analysic plz help me
Name | Year | Slab | Commission |
Peter | 2014 | $0-$50000 | 1% |
peter | 2014 | $50001-$100000 | 1.5% |
peter | 2014 | $100001-$150000 | 2% |
peter | 2014 | >$150001 | 2.5% |
john | 2014 | $0-$50000 | 1.5% |
like this the commision varies plz help
-lohi
hi jagan ,
given attachment we got cumulative sum but the reqmnt is we have to add a commission field that may differs to all user (Commission may vary depends on users ) so how to give dynamic commission depending on users
for example
john may have >=$5000 commission =1%
peter may have >=$5000 commission =2%
-lohi
hi QV's ,
Any idea on this dynamic content,
-lohi
Hi,
Try this script
Temp:
LOAD Name,
Date,
year(Date) as Year,
Sales
FROM
[Data (3).xlsx]
(ooxml, embedded labels, table is [Dummy Sales]);
CumulativeSum:
LOAD
*,
If(CumulativeSum > 150001, '>$150001',
If(CumulativeSum >= 100001, '$100001-$150000',
If(CumulativeSum >= 50001, '$50001-$100000',
If(CumulativeSum >= 0, '$0-$50000')))) AS Slab;
LOAD
Name,
Date,
Year,
Sales,
If(Name <> Previous(Name) or Year <> Previous(Year), Sales, RangeSum(Sales, Peek('CumulativeSum'))) AS CumulativeSum;
LOAD
*
Resident Temp
Order by Name, Date;
DROP Table Temp;
Left Join(CumulativeSum)
LOAD
Name, Year, Slab,
Num(Num#(Commission, '#.##%'), '#.00') AS Commission
INLINE [
Name, Year, Slab, Commission
Peter, 2014, $0-$50000, 1%
Peter, 2014, $50001-$100000, 1.50%
Peter, 2014, $100001-$150000, 2%
Peter, 2014, >$150001, 2.50%
Tom, 2014, $0-$50000, 1.50%
];
Hope this helps you.
Regards,
Jagan.
hi jagan,
thxs for reply i have worked on this but the reqmnt is if we have millon names then we cannot create that many in inline right, the point is we have to use interval match fuction between commission and cumulative sum its should loop and check . i dont knw for loop thts why iam strucked
-lohi
Hi,
I did this in Inline because your excel file has no data, you can change the script to load from your DataSource, I think this is enough no need of IntervalMatch().
Regards,
Jagan.
Temp:
LOAD Name,
Date,
year(Date) as Year,
Sales
FROM
(ooxml, embedded labels, table is [Dummy Sales]);
Temp1:
NoConcatenate
LOAD
*
Resident Temp
Order by Name, Date;
DROP Table Temp;
CumulativeSum:
NoConcatenate
LOAD
Name,
Date,
Year,
Sales,
If(Name <> Previous(Name) or Year <> Previous(Year), Sales, RangeSum(Sales, Peek('CumulativeSum'))) AS CumulativeSum
Resident Temp1 Order by Name,Date;
DROP Table Temp1;
dd1:
LOAD * INLINE [
StartR,EndR,Commision
0,50000,1%
50001,100000,2%
100001,150000,3%
150001,200000,4%
];
IntervalMatch (CumulativeSum) LOAD StartR as Start,EndR Resident dd1 ;
here my problem is dynamicly we can change the Cummision if i want to give peter 10% i should do tht , how can i do tht ?
-lohi
Hi,
Can you attach your sample Commision data in Excel file? Your commision data should consists of StartR,EndR, Name, Commision, then only we can do this.
Regards,
Jagan.
hi jagan ,
Please find attachment
- lohi
hi jagan ,
in the attachment Slab needs to check Cumulative sum and then it should check Name and Year and final its should get Commission .
-lohi
hi jagan ,
If data is like this how to do ,
Name | Year | Start | End | Commission |
Peter | 2014 | 0 | 60000 | 1.0% |
Peter | 2014 | 60001 | 100000 | 1.5% |
Peter | 2014 | 100001 | 150000 | 2.0% |
Peter | 2015 | 0 | 50000 | 1.0% |
Peter | 2015 | 50001 | 100000 | 1.5% |
Peter | 2015 | 100001 | 150000 | 2.0% |
Tom | 2014 | 0 | 50000 | 1.2% |
Tom | 2014 | 50001 | 100000 | 1.6% |
Tom | 2014 | 100001 | 150000 | 2.5% |
Tom | 2015 | 0 | 50000 | 1.2% |
Tom | 2015 | 50001 | 100000 | 1.6% |
Tom | 2015 | 100001 | 150000 | 2.5% |
Mark | 2014 | 0 | 50000 | 0.8% |
Mark | 2014 | 50001 | 80000 | 1.4% |
Mark | 2014 | 80001 | 140000 | 2.0% |
Mark | 2015 | 0 | 50000 | 0.8% |
Mark | 2015 | 50001 | 80000 | 1.4% |
Mark | 2015 | 80001 | 140000 | 2.0% |