Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I am trying to generate the Opening and Closing balance for the trading. I am not able to get the correct results in the below method using peek and previous.
tempsales:
LOAD DD_Date as OC_Date,DD_Day as Day
Resident DATE_DIM;
left join(tempsales)
tempsales:
LOAD Date as OC_Date,SUM(IF(S_Total_Amount=0,0,S_Total_Amount)) AS Sales
Resident Sales
group by Date;
left join(tempsales)
tempsales:
LOAD Date as OC_Date,SUM(IF([P_Total Amount]=0,0,[P_Total Amount])) AS Purchase
Resident Purchase
group by Date;
NEWTABLE:
LOAD OC_Date,Day,ALT(Sales,0) AS Sales,ALT(Purchase,0) AS Purchase ,if(IsNull(Previous(OC_Date)) ,6021121,Peek(Closing_Bal)) AS Opening_Bal,
if(IsNull(Previous(OC_Date)) or OC_Date<>Previous(OC_Date),6021121+Alt(Purchase,0)-Alt(Sales,0),Peek(Closing_Bal)-Alt(Sales,0)+Alt(Purchase,0)) AS Closing_Bal
Resident tempsales order by OC_Date;
drop table tempsales;
In this above method my closing balance is subtracting the previous day sales and purchase as well and also when there is no data for sales and purchase then it is taking the opening balance.
Can anyone please help me on this issue?
Regards,
viswa
Hi,
Are you looking for following
NoConcatenate
Opening_Closing_Bal_Tmp:
LOAD *
Inline [OC_Date, Sales,Purchase
7/1/2017, 0, 0
7/2/2017, 0, 0
7/3/2017, 0, 0
7/4/2017, 0, 0
7/5/2017, 13622, 0
7/6/2017, 7.93, 106545.25
7/7/2017, 128934.29, 62323.32
7/8/2017, 112321.23, 0
7/9/2017, 0, 0
7/10/2017, 115441, 174521.21];
SET vOpeningBal = 621111;
SET vClosingBal = 0;
SET vRowCount = 0;
LET vRowCount = NoOfRows('Opening_Closing_Bal_Tmp');
FOR i = 0 to $(vRowCount)-1
LET vOC_Date = Peek('OC_Date', $(i), 'Opening_Closing_Bal_Tmp');
LET vSales = Peek('Sales', $(i), 'Opening_Closing_Bal_Tmp');
LET vPurchase = Peek('Purchase', $(i), 'Opening_Closing_Bal_Tmp');
LET vClosingBal=$(vOpeningBal) + Peek('Purchase', $(i), 'Opening_Closing_Bal_Tmp') - Peek('Sales', $(i), 'Opening_Closing_Bal_Tmp');
[Opening_Closing_Bal]:
LOAD *
Inline [OC_Date, Sales, Purchase, Opening_Balance, Closing_Balance
$(vOC_Date), $(vSales), $(vPurchase), $(vOpeningBal), $(vClosingBal)];
LET vOpeningBal=$(vClosingBal);
NEXT i;
DROP Table Opening_Closing_Bal_Tmp;
HI Viru,
I am taking the fields sales and purchase from different table and when i try that it is not working.
Opening_Closing_Bal_Tmp:
LOAD DD_Date as OC_Date,DD_Day as Day
Resident DATE_DIM;
left join(Opening_Closing_Bal_Tmp)
Opening_Closing_Bal_Tmp:
LOAD Date as OC_Date,SUM(IF(S_Total_Amount=0,0,S_Total_Amount)) AS Sales
Resident Sales
group by Date;
left join(Opening_Closing_Bal_Tmp)
Opening_Closing_Bal_Tmp:
LOAD Date as OC_Date,SUM(IF([P_Total Amount]=0,0,[P_Total Amount])) AS Purchase
Resident Purchase
group by Date;
SET vOpeningBal = 621111;
SET vClosingBal = 0;
SET vRowCount = 0;
LET vRowCount = NoOfRows('Opening_Closing_Bal_Tmp');
FOR i = 0 to $(vRowCount)-1
LET vOC_Date = Peek('OC_Date', $(i), 'Opening_Closing_Bal_Tmp');
LET vSales = Peek('Sales', $(i), 'Opening_Closing_Bal_Tmp');
LET vPurchase = Peek('Purchase', $(i), 'Opening_Closing_Bal_Tmp');
LET vClosingBal=$(vOpeningBal) + Peek('Purchase', $(i), 'Opening_Closing_Bal_Tmp') - Peek('Sales', $(i), 'Opening_Closing_Bal_Tmp');
[Opening_Closing_Bal]:
LOAD *
Inline [OC_Date, Sales, Purchase, Opening_Balance, Closing_Balance
$(vOC_Date), $(vSales), $(vPurchase), $(vOpeningBal), $(vClosingBal)];
LET vOpeningBal=$(vClosingBal);
NEXT i;
DROP Table Opening_Closing_Bal_Tmp;
Your screen output is my expected results.
Hi,
Can you please share your sample data file. It will allow me to modify the script accordingly.
Thanks,
Vijay