Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all!
I have a table like:
RandomData:
Load * Inline [
Date, Nature, Amount
08.01.2017, %paid, 3000
08.01.2017, loan repaid, 5000
10.12.2016, %paid, 3000
10.12.2016, loan repaid, 7000
05.11.2016, %paid, 3000
05.11.2016, loan repaid, 9000
03.10.2016, %paid, 3000
03.10.2016, loan repaid, 5000
10.09.2016, %paid, 3000
10.09.2016, loan repaid, 5000
]
I need to
1) Group it by Date (or maybe distinct by date)
2) distribute Amount to new columns -> [%paid] and [loan repaid]
3) make a new column - [overpaid] where show amounts larger than 5000 (excess)
So the result should be:
Date, %paid, loan repaid, overpaid
08.01.2017, 3000, 5000, 0
10.12.2016, 3000, 5000, 2000
05.11.2016, 3000, 5000, 4000
03.10.2016, 3000, 5000, 0
10.09.2016, 3000, 5000, 0
]
Please, help me with that......
Here is another option
SET DateFormat='DD.MM.YYYY';
RandomData:
Load * Inline [
Date, Nature, Amount
08.01.2017, %paid, 3000
08.01.2017, loan repaid, 5000
10.12.2016, %paid, 3000
10.12.2016, loan repaid, 7000
05.11.2016, %paid, 3000
05.11.2016, loan repaid, 9000
03.10.2016, %paid, 3000
03.10.2016, loan repaid, 5000
10.09.2016, %paid, 3000
10.09.2016, loan repaid, 5000
];
tmpTable:
LOAD Distinct Date
Resident RandomData;
For i = 1 to FieldValueCount('Nature')
LET vField = FieldValue('Nature', $(i));
TRACE $(vField);
Left Join (tmpTable)
LOAD Distinct Date,
Amount as [$(vField)]
Resident RandomData
Where Nature = '$(vField)';
NEXT
FinalTable:
LOAD *,
5000 as [loan repaid new],
[loan repaid] - 5000 as overpaid
Resident tmpTable;
DROP Tables RandomData, tmpTable;
DROP Field [loan repaid];
RENAME Field [loan repaid new] to [loan repaid];
RandomData:
Generic Load * Inline [
Date, Nature, Amount
08.01.2017, %paid, 3000
08.01.2017, loan repaid, 5000
10.12.2016, %paid, 3000
10.12.2016, loan repaid, 7000
05.11.2016, %paid, 3000
05.11.2016, loan repaid, 9000
03.10.2016, %paid, 3000
03.10.2016, loan repaid, 5000
10.09.2016, %paid, 3000
10.09.2016, loan repaid, 5000
];
Set vListOfTables = ;
For vTableNo = 0 to NoOfTables()-1
Let vTableName = TableName($(vTableNo)) ;
If Subfield(vTableName,'.',1)='RandomData' Then
Let vListOfTables = vListOfTables & If(Len(vListOfTables)>0,',') & Chr(39) & vTableName & Chr(39) ;
End If
Next vTableNo
CombinedGenericTable:
LOAD Distinct Date Inline [
Date, Nature, Amount
08.01.2017, %paid, 3000
08.01.2017, loan repaid, 5000
10.12.2016, %paid, 3000
10.12.2016, loan repaid, 7000
05.11.2016, %paid, 3000
05.11.2016, loan repaid, 9000
03.10.2016, %paid, 3000
03.10.2016, loan repaid, 5000
10.09.2016, %paid, 3000
10.09.2016, loan repaid, 5000
];
For each vTableName in $(vListOfTables)
Left Join (CombinedGenericTable) Load * Resident [$(vTableName)];
Drop Table [$(vTableName)];
Next vTableName
After this Resident Load CombinedGenericTable and create your new column of overpaid as you want to
Also read
Here is another option
SET DateFormat='DD.MM.YYYY';
RandomData:
Load * Inline [
Date, Nature, Amount
08.01.2017, %paid, 3000
08.01.2017, loan repaid, 5000
10.12.2016, %paid, 3000
10.12.2016, loan repaid, 7000
05.11.2016, %paid, 3000
05.11.2016, loan repaid, 9000
03.10.2016, %paid, 3000
03.10.2016, loan repaid, 5000
10.09.2016, %paid, 3000
10.09.2016, loan repaid, 5000
];
tmpTable:
LOAD Distinct Date
Resident RandomData;
For i = 1 to FieldValueCount('Nature')
LET vField = FieldValue('Nature', $(i));
TRACE $(vField);
Left Join (tmpTable)
LOAD Distinct Date,
Amount as [$(vField)]
Resident RandomData
Where Nature = '$(vField)';
NEXT
FinalTable:
LOAD *,
5000 as [loan repaid new],
[loan repaid] - 5000 as overpaid
Resident tmpTable;
DROP Tables RandomData, tmpTable;
DROP Field [loan repaid];
RENAME Field [loan repaid new] to [loan repaid];
Not a problem buddy
Can you just try both methods with your data and let us know the reload times for both.
Just keen to know
Thanks
V
I agree with the testing part... I never got a chance to test this, but I have always found Generic Load to be fairly complex. I mean you break them into different table and then use complex For Loop logic to join them back. Why not just use For Loop upfront. But having said that, I am sure there ought to be some performance difference (when there is lots of data at hand, for smaller databases, I wouldn't see much difference)
I second what you said Sunny ; I think the determining factor would be how FieldValue() performs.