Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I am facing one issue with one logic :
Logic is :
If ID= Daily Allowance,Daily Deduction in Expense. I want (Daily Allowance - Daily Deduction) and Store this In Daily Allowance and remove Daily Deduction for Every ID.
How to solve this.Caan anybody tell me solution for this.
For ref Find below attched file
Ah, you change your requirements and your example data...
The solution is simple: filter the rows that you want to group:
RawData:
LOAD ID,
Expense,
Amount
FROM Issue.xlsx (ooxml, embedded labels, table is Sheet1);
GroupedRecs:
NOCONCATENATE
LOAD ID, Sum(Amount) AS Amount2,
'Daily Allowance' AS Expense2
RESIDENT RawData
WHERE Match(Expense, 'Daily Allowance', 'Daily Deduction')
GROUP BY ID;
And if you want to keep all other Expense types in this table, add them back to your Grouped records by adding this statement at the end:
CONCATENATE (GroupedRecs)
LOAD ID, Expense AS Expense2, Amount AS Amount2
RESIDENT RawData
WHERE NOT Match(Expense, 'Daily Allowance', 'Daily Deduction');
Result:
Now you can drop the original table if you don't need it anymore. It stays put in my attachment because it lets you compare INPUT Data to Results
Best,
Peter
Daily Deduction already seems to be a negative value. Do you really want Daily Allowance - Daily Deduction?
If not, this script will perform as you want:
RawData:
LOAD ID,
Expense,
Amount
FROM Issue.xlsx (ooxml, embedded labels, table is Sheet1);
GroupedRecs:
NOCONCATENATE
LOAD ID, Sum(Amount) AS Amount2,
'Daily Allowance' AS Expense2
RESIDENT RawData
GROUP BY ID;
Result:
See attachment.
Best,
Peter
Hi,
try like,
use fabs()
Daily Allowance - fabs(Dailly Deduction)
Regards
Hi,
try like
RawData:
LOAD ID,
//Expense as Daily_Allowance,
Amount as Daily_Allowance
FROM Issue.xlsx (ooxml, embedded labels, table is Sheet1)
where wildmatch(Expense,'Daily Allowance');
left join
LOAD ID,
//Expense as Daily_Deduction,
Amount as Daily_Deduction
FROM Issue.xlsx (ooxml, embedded labels, table is Sheet1)
where wildmatch(Expense,'Daily Deduction');
Final:
Load ID,
Daily_Allowance - Daily_Deduction as Final_Expense
Resident RawData;
Drop table RawData;
REgards
If i do
'Daily Allowance' AS Expense2.I can't get my requirement.
I have so many expance type If i ahve only two expenses it will work.
Find my attachment.
Can any one help tell me how tp do this?
Ah, you change your requirements and your example data...
The solution is simple: filter the rows that you want to group:
RawData:
LOAD ID,
Expense,
Amount
FROM Issue.xlsx (ooxml, embedded labels, table is Sheet1);
GroupedRecs:
NOCONCATENATE
LOAD ID, Sum(Amount) AS Amount2,
'Daily Allowance' AS Expense2
RESIDENT RawData
WHERE Match(Expense, 'Daily Allowance', 'Daily Deduction')
GROUP BY ID;
And if you want to keep all other Expense types in this table, add them back to your Grouped records by adding this statement at the end:
CONCATENATE (GroupedRecs)
LOAD ID, Expense AS Expense2, Amount AS Amount2
RESIDENT RawData
WHERE NOT Match(Expense, 'Daily Allowance', 'Daily Deduction');
Result:
Now you can drop the original table if you don't need it anymore. It stays put in my attachment because it lets you compare INPUT Data to Results
Best,
Peter
Thank you so much Peter