Find Previous Balance and End Balance by Date on Upload

[Ribeiro]

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

[stevejoyce · Accepted solution]

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;

[Ribeiro]

😀

Thank you very much

[Ribeiro]

failed to treat if it is null

🤔

 

[Ribeiro]

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!

[stevejoyce · Accepted solution]

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;