Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I notice strange behaviour when performing a preceding load on a table generated using the peek function? Can anyone explain what's happening and why? @hic do you have an explanation?
Example 1. Not using preceding load, the peek works at intended. On the Version 3 ValidTo I get the version 4 ValidFrom figures.
Data: LOAD
rowno() as Id,
* inline [ Version, Location, ValidFrom 3, Uppsala, 2019-04-18 3, Storvreta, 2019-04-18 3, Tierp, 2019-04-18 4, Uppsala, 2019-04-19 4, Storvreta, 2019-04-19 4, Tierp, 2019-04-19 ]; PeekTable: LOAD Id, Version, Location, ValidFrom, If(peek('Location')=Location, peek('ValidFrom'), '9999-12-31') as ValidTo Resident Data ORDER BY Location, Version desc; DROP TABLE Data;
Example 2. Adding preceding load
But if I add a preceeding load to my my peek table something strange happens.... all ValidTo ends up as 9999-12-31.Output using preceding load on peek
Data: LOAD
rowno() as Id,
* inline [ Version, Location, ValidFrom 3, Uppsala, 2019-04-18 3, Storvreta, 2019-04-18 3, Tierp, 2019-04-18 4, Uppsala, 2019-04-19 4, Storvreta, 2019-04-19 4, Tierp, 2019-04-19 ]; Peek: LOAD Id, ValidTo ; PeekTable: LOAD Id, Version, Location, ValidFrom, If(peek('Location')=Location, peek('ValidFrom'), '9999-12-31') as ValidTo Resident
Data ORDER BY Location, Version desc;//DROP TABLE Data;
My workaround
Data: LOAD rowno() as Id, * inline [ Version, Location, ValidFrom 3, Uppsala, 2019-04-18 3, Storvreta, 2019-04-18 3, Tierp, 2019-04-18 4, Uppsala, 2019-04-19 4, Storvreta, 2019-04-19 4, Tierp, 2019-04-19 ]; PeekTable: LOAD Id, Version, Location, ValidFrom, If(peek('Location')=Location, peek('ValidFrom'), '9999-12-31') as ValidTo Resident Data ORDER BY Location, Version desc; Final: LOAD Id, ValidTo Resident PeekTable ; DROP Table PeekTable;
May be try this
Operations:
LOAD Customer_Slab,
Customer_Name,
Customer_Account_no,
Customer_Sales,
Total_Purchase,
If(Customer_Name = Previous(Customer_Name), RangeMax(RangeSum(Peek('result'), -Customer_Sales), 0), Total_Purchase - Customer_Sales) as result;
LOAD * INLINE [
Customer_Slab, Customer_Name, Customer_Account_no, Customer_Sales, Total_Purchase, ZERO_COLUM
7, Eric, Boston, 450, 5000, 0
6, Eric, Boston, 500, 0, 0
5, Eric, Boston, 320, 0, 0
4, Eric, Boston, 250, 0, 0
3, Eric, Boston, 140, 0, 0
2, Eric, Boston, 659, 0, 0
1, Eric, Boston, 370, 0, 0
7, Monti, Kreta, 220, 2500, 0
6, Monti, Kreta, 220, 0, 0
5, Monti, Kreta, 180, 0, 0
4, Monti, Kreta, 840, 0, 0
3, Monti, Kreta, 1, 0, 0
2, Monti, Kreta, 456, 0, 0
1, Monti, Kreta, 6986, 0, 0
];
But Sir, I'm using qvd except for an inline table because I have many data of the same. When Put the following into it. its descending order will be disturbed.
If(Customer_Name = Previous(Customer_Name), RangeMax(RangeSum(Peek('result'), -Customer_Sales), 0), Total_Purchase - Customer_Sales) as result;
this is my code.
CASH_ADJUSTED_TOTAL:
NoConcatenate
LOAD SLAB,
ELEMENT_ID,
ACCOUNT_NUMBER,
TARIFF_DEDUCTED,
CASH_BALANCE,
// RangeMax(RangeSum(Alt(Peek('result'), CASH_BALANCE), -Rangesum(TARIFF_DEDUCTED)), 0) as result;
If(ELEMENT_ID = Previous(ELEMENT_ID) and Previous(ACCOUNT_NUMBER) = ACCOUNT_NUMBER, RangeMax(RangeSum(Peek('result'), -TARIFF_DEDUCTED), 0), CASH_BALANCE - TARIFF_DEDUCTED) as result;
LOAD SLAB,
ELEMENT_ID,
ACCOUNT_NUMBER,
(SLAB & ELEMENT_ID & ACCOUNT_NUMBER) AS ORDER,
TARIFF_DEDUCTED,
SUM_TOTAL_AMOUNT,
MAX_SLAB,
If(STATUS = 'N' AND SLAB = MAX_SLAB, SUM_TOTAL_AMOUNT, 0) AS CASH_BALANCE,
If(STATUS = 'N', 'Y', 'N') AS STATUS
Resident CASH_DEPOSIT_TOTAL
ORDER BY ORDER DESC;
Not sure I understand your concern?
I'm using qvd except for an inline table because I have many data of the same. its descending order and result are disturbed.
Whenever I used particular Element id and Account number, its provide correct result and perfect descending order.
CASH_ADJUSTED_TOTAL:
NoConcatenate
LOAD SLAB,
ELEMENT_ID,
ACCOUNT_NUMBER,
TARIFF_DEDUCTED,
CASH_BALANCE,
// RangeMax(RangeSum(Alt(Peek('result'), CASH_BALANCE), -Rangesum(TARIFF_DEDUCTED)), 0) as result;
If(ELEMENT_ID = Previous(ELEMENT_ID) and Previous(ACCOUNT_NUMBER) = ACCOUNT_NUMBER, RangeMax(RangeSum(Peek('result'), -TARIFF_DEDUCTED), 0), CASH_BALANCE - TARIFF_DEDUCTED) as result;
LOAD SLAB,
ELEMENT_ID,
ACCOUNT_NUMBER,
(SLAB & ELEMENT_ID & ACCOUNT_NUMBER) AS ORDER,
TARIFF_DEDUCTED,
SUM_TOTAL_AMOUNT,
MAX_SLAB,
If(STATUS = 'N' AND SLAB = MAX_SLAB, SUM_TOTAL_AMOUNT, 0) AS CASH_BALANCE,
If(STATUS = 'N', 'Y', 'N') AS STATUS
Resident CASH_DEPOSIT_TOTAL
ORDER BY ORDER DESC;
result are disturbed? Can you show an image or a sample? I won't really know what disturbed means unless you provide more details
Desire result:
ELEMENT_ID | ACCOUNT_NUMBER | SLAB | TARIFF_DEDUCTED | CASH_BALANCE | result |
03277 | 73 | Slab 7 | 54558.86 | 567000000 | 566945441.14 |
03277 | 73 | Slab 6 | 180729830.3395 | 0 | 386215610.8005 |
03277 | 73 | Slab 5 | 180729830.3395 | 0 | 205485780.461 |
03277 | 73 | Slab 4 | 180729830.3395 | 0 | 24755950.1215 |
03277 | 73 | Slab 3 | 180729830.3395 | 0 | 0 |
03277 | 73 | Slab 2 | 180729830.3395 | 0 | 0 |
03277 | 73 | Slab 1 | 180729830.3395 | 0 | 0 |
Please check the following which produced disturbing result and find the attache qvd.
DATA:
LOAD ELEMENT_ID,
ACCOUNT_NUMBER,
SUM(TOTAL_AMOUNT) AS SUM_TOTAL_AMOUNT,
MAX(SLAB) AS MAX_SLAB
FROM [QVDS\OUTPUT_1.qvd] (qvd)
WHERE REC_STATUS = 'N'
GROUP BY ELEMENT_ID,
ACCOUNT_NUMBER;
INNER JOIN
LOAD TARIFF_DEDUCTED,
SLAB,
ELEMENT_ID,
ACCOUNT_NUMBER,
(ELEMENT_ID & ACCOUNT_NUMBER & SLAB) AS ORDER
FROM [QVDS\OUTPUT_1.qvd] (qvd);
CASH_ADJUSTED_TOTAL:
NoConcatenate
LOAD DISTINCT
ELEMENT_ID,
ACCOUNT_NUMBER,
'Slab ' & SLAB AS SLAB,
TARIFF_DEDUCTED,
CASH_BALANCE,
If(ELEMENT_ID = Previous(ELEMENT_ID) and ACCOUNT_NUMBER = Previous(ACCOUNT_NUMBER), RangeMax(RangeSum(Peek('result'), -TARIFF_DEDUCTED), 0), RangeMax((CASH_BALANCE - TARIFF_DEDUCTED), 0)) as result,
;
LOAD SLAB,
ELEMENT_ID,
ACCOUNT_NUMBER,
TARIFF_DEDUCTED,
MAX_SLAB,
If(SLAB = MAX_SLAB, SUM_TOTAL_AMOUNT, 0) AS CASH_BALANCE
Resident DATA
ORDER BY ORDER DESC;
DROP Table DATA;
ELEMENT_ID | ACCOUNT_NUMBER | SLAB | TARIFF_DEDUCTED | CASH_BALANCE | result |
03277 | 73 | Slab 7 | 54558.86 | 567000000 | 566618088 |
03277 | 73 | Slab 7 | 54558.86 | 567000000 | 566672646.8 |
03277 | 73 | Slab 7 | 54558.86 | 567000000 | 566727205.7 |
03277 | 73 | Slab 7 | 54558.86 | 567000000 | 566781764.6 |
03277 | 73 | Slab 7 | 54558.86 | 567000000 | 566836323.4 |
03277 | 73 | Slab 7 | 54558.86 | 567000000 | 566890882.3 |
03277 | 73 | Slab 7 | 54558.86 | 567000000 | 566945441.1 |
03277 | 73 | Slab 6 | 180729830.3 | 0 | 0 |
03277 | 73 | Slab 6 | 180729830.3 | 0 | 24428596.96 |
03277 | 73 | Slab 6 | 180729830.3 | 0 | 205158427.3 |
03277 | 73 | Slab 6 | 180729830.3 | 0 | 385888257.6 |
03277 | 73 | Slab 5 | 180729830.3 | 0 | 0 |
03277 | 73 | Slab 4 | 180729830.3 | 0 | 0 |
03277 | 73 | Slab 3 | 180729830.3 | 0 | 0 |
03277 | 73 | Slab 2 | 180729830.3 | 0 | 0 |
03277 | 73 | Slab 1 | 180729830.3 | 0 | 0 |
Where is the qvd?
QVDS_DATA:
LOAD * FROM [QVDS\OUTPUT_1.qvd] (qvd);
ELEMENT_ID | ACCOUNT_NUMBER | SLAB | TARIFF_DEDUCTED | REC_STATUS | TOTAL_AMOUNT |
03277 | 73 | 1 | 180729830.3 | N | 0 |
03277 | 73 | 2 | 180729830.3 | N | 0 |
03277 | 73 | 3 | 180729830.3 | N | 0 |
03277 | 73 | 4 | 180729830.3 | N | 0 |
03277 | 73 | 5 | 180729830.3 | N | 0 |
03277 | 73 | 6 | 180729830.3 | N | 0 |
03277 | 73 | 7 | 54558.86 | N | 81000000 |
When open the qvd data, it has multiple record. I'm shocked that how to do it correct
I resolved by myself 🙂
Thanks for your support earlier.