Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I would like to create a Previous Balance and Current Balance by Date Same as the example below. If possible doing it when loading the data.
Table:
LOAD [
Date, Cod, Type, Qtde
10/10/2012, 46, E, 5
10/10/2012, 46, S, 3
19/02/2014, 46, E, 4
19/02/2014, 46, S, 6
20/02/2015, 46, E, 3
20/02/2015, 46, S, 1
];
Date | Cod | previous | Type E | Type S | Current |
10/10/2012 | 46 | 0 | 5 | 3 | 2 |
19/02/2014 | 46 | 2 | 4 | 6 | 4 |
20/02/2015 | 46 | 4 | 3 | 1 | 6 |
How do you calculated Current?
Anyway, first i'd use generic load to unpivot.
Then calculate Current.
Then use peek() to get the preivious current.
Table:
LOAD * inline [
Date, Cod, Type, Qtde
10/10/2012, 46, E, 5
10/10/2012, 46, S, 3
19/02/2014, 46, E, 4
19/02/2014, 46, S, 6
20/02/2015, 46, E, 3
20/02/2015, 46, S, 1
];
//1) Unpivot
//generic load
final_table_unpivot:
generic Load
Date,
Cod,
'Type ' & Type,
Qtde
resident Table
order by Date asc;
//Unpivot
set vListOfTables = ;
for vTableNo = 0 to NoOfTables()
let vTableName = Tablename($(vTableNo));
If Subfield(vTableName,'.',1)='final_table_unpivot' Then
Let vListOfTables = vListOfTables & If(Len(vListOfTables)>0,',') & Chr(39) & vTableName & Chr(39) ;
End If
Next vTableNo
;
trace vTableName $(vTableName);
final_table:
load distinct
Date,
Cod
resident Table;
For each vTableName in $(vListOfTables)
Left Join (final_table) Load * Resident [$(vTableName)];
Drop Table [$(vTableName)];
Next vTableName
drop tables Table;
final_table_unpivot_calculations:
//Calculate previous based on current
load
*
,if(rowno() = 1, 0, peek(Current)) as previous
;
//2) Calculate current
load
*
,[Type E] - [Type S] as Current
resident final_table;
drop table final_table;
you can handle in this part below using alt()
//generic load
final_table_unpivot:
generic Load
Date,
Cod,
'Type ' & Type,
alt(Qtde,0)
resident Table
order by Date asc;
How do you calculated Current?
Anyway, first i'd use generic load to unpivot.
Then calculate Current.
Then use peek() to get the preivious current.
Table:
LOAD * inline [
Date, Cod, Type, Qtde
10/10/2012, 46, E, 5
10/10/2012, 46, S, 3
19/02/2014, 46, E, 4
19/02/2014, 46, S, 6
20/02/2015, 46, E, 3
20/02/2015, 46, S, 1
];
//1) Unpivot
//generic load
final_table_unpivot:
generic Load
Date,
Cod,
'Type ' & Type,
Qtde
resident Table
order by Date asc;
//Unpivot
set vListOfTables = ;
for vTableNo = 0 to NoOfTables()
let vTableName = Tablename($(vTableNo));
If Subfield(vTableName,'.',1)='final_table_unpivot' Then
Let vListOfTables = vListOfTables & If(Len(vListOfTables)>0,',') & Chr(39) & vTableName & Chr(39) ;
End If
Next vTableNo
;
trace vTableName $(vTableName);
final_table:
load distinct
Date,
Cod
resident Table;
For each vTableName in $(vListOfTables)
Left Join (final_table) Load * Resident [$(vTableName)];
Drop Table [$(vTableName)];
Next vTableName
drop tables Table;
final_table_unpivot_calculations:
//Calculate previous based on current
load
*
,if(rowno() = 1, 0, peek(Current)) as previous
;
//2) Calculate current
load
*
,[Type E] - [Type S] as Current
resident final_table;
drop table final_table;
😀
Thank you very much
failed to treat if it is null
🤔
Date, Cod, Type, Qtde
10/10/2012, 46, E, 5
10/10/2012, 46, S, 3
19/02/2014, 46, E, 4
19/02/2014, 46, S, 6
20/02/2015, 46, E, 3
20/02/2015, 46, S, 1
20/03/2016, 46, S, 2
20/03/2016, 46, E,
];
need to include zero when null Help!
you can handle in this part below using alt()
//generic load
final_table_unpivot:
generic Load
Date,
Cod,
'Type ' & Type,
alt(Qtde,0)
resident Table
order by Date asc;