Preceding load on peek() tables

[Vegar]

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;

[sunny_talwar]

I have noticed this behavior where if you are using Peek on a field, you cannot really remove the field you are peeking on in your preceding load.... another work around this is 

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,
  ValidFrom,
  Location
;
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 Field ValidFrom, Location From PeekTable;

Since you are doing peek on Location and ValidFrom, bring them in your preceding load, but then drop the field using DROP Field statement.

[marcus_sommer]

It's a specially behaviour of peek() and not really related to the preceeding load else to the fact that the fields Location and ValidFrom from the peek() aren't included within the preceeding - just add them within the preceeding too or remove them from the start-load and it will work respectively fail, too.

I think the reason for it is that the interrecord-functions needs these fields directly within the loading table and there they aren't loaded. In this way they are different to other "normal" functions like: if(F1 = 'A', F2, F3) as F4 which could be evaluated on the source-level.

- Marcus

[Gysbert_Wassenaar · Accepted solution]

Peek looks into the target table. An intermediate table that forwards the data to the preceding load is not the target table. That will be the result of the preceding load. The field you're trying to peek into isn't in the target table. Arguably a bug. But it's hard to expect peek to know what fields do or don't exist x steps after peek executes.

[Vegar]

Thank you for your imputs @sunny_talwar, @marcus_sommer  and @Gysbert_Wassenaar . They where all helpful.

@Gysbert_Wassenaar wrote:
Peek looks into the target table. An intermediate table that forwards the data to the preceding load is not the target table. That will be the result of the preceding load. The field you're trying to peek into isn't in the target table. 

---

Your answer makes me adjust how I look at the consept preceding loads. I've always imagined the  first load to close before going up to the next preceding load, but after reading your comment  I get the idea that the entries are of moving up the preceding load ladder row by row. 

This is also kind of explains the problem with rowno() in preceding loads.

[somacdc]

I have been using Preceding load for my following desire result.

Customer_Name	Customer_Place	Customer_Sales	Customer_Aggr	Total_Purchase	result
Eric	Boston	450	0	5000	5000-0=5000
John	Kreta	500	950	0	5000-950=4050
Rick	Texas	320	820	0	4050-820=3230
Martin	Ohio	250	570	0	3230-570=2660
Marcus	Kensas	140	390	0	2660-390=2270
Lisa	Boston	659	799	0	2270-799=1471
Geo	Kreta	370	1029	0	1471-1029=230
Monti	Kreta	220	590	0	230-590=0
Carl	Kensas	220	400	0	0-400=0
Daniel	Texas	180	400	0	0-400=0
Adi	Kensas	840	1060	0	0-1060=0
Begoli	Ohio	1	841	0	0-841=0
Bruce	Texas	456	457	0	0-457=0
Hillary	Kreta	6986	7442	0	0-7442=0

Please check my following techniques of result column and correct the same.

Operations:
Load
Customer_Name,
Customer_Place,
Customer_Sales,
Total_Purchase,
Rangesum(Total_Purchase - Peek(Customer_Aggr)) as result,
Rangesum( Customer_Sales + Peek(Customer_Sales,-1)) AS Customer_Aggr;

//*1.* Here I'm creating my data
//ExampleData:
Load * Inline [
Customer_ID, Customer_Name, Customer_Place, Customer_Sales, Total_Purchase
1,Eric,Boston,450,1000
2,John,Kreta,500,0
3,Rick,Texas,320,0
4,Martin,Ohio,250,0
5,Marcus,Kensas,140,0
6,Lisa,Boston,659,0
7,Geo,Kreta,370,0
8,Monti,Kreta,220,0
9,Daniel,Texas,180,0
10,Carl,Kensas,220,0
11,Adi,Kensas,840,0
12,Begoli,Ohio,1,0
13,Bruce,Texas,456,0
14,Hillary,Kreta,6986,0];

////*3.* Finally, I'm trying to safe the table temporarilly and order by Customer_Aggr
NoConcatenate
OrderAttemp:
LOAD * Resident Operations Order By Customer_Aggr;


DROP Table Operations;

[sunny_talwar]

Total purchase for Eric changed from 1000 in the inline table to 5000 in your table? Is that a typo, or is that some kind of logic?

[somacdc]

ohh. Sorry. Please consider 5000 in the inline table

[sunny_talwar]

Try this

Operations:
LOAD Customer_ID,
	 Customer_Name,
	 Customer_Place,
	 Customer_Sales,
	 Total_Purchase,
	 Rangesum(Customer_Sales + Peek(Customer_Sales,-1)) AS Customer_Aggr,
	 RangeMax(RangeSum(Alt(Peek('result'), Total_Purchase), -Rangesum(Customer_Sales + Peek(Customer_Sales,-1))), 0) as result;
LOAD * INLINE [
    Customer_ID, Customer_Name, Customer_Place, Customer_Sales, Total_Purchase
    1, Eric, Boston, 450, 5000
    2, John, Kreta, 500, 0
    3, Rick, Texas, 320, 0
    4, Martin, Ohio, 250, 0
    5, Marcus, Kensas, 140, 0
    6, Lisa, Boston, 659, 0
    7, Geo, Kreta, 370, 0
    8, Monti, Kreta, 220, 0
    9, Daniel, Texas, 180, 0
    10, Carl, Kensas, 220, 0
    11, Adi, Kensas, 840, 0
    12, Begoli, Ohio, 1, 0
    13, Bruce, Texas, 456, 0
    14, Hillary, Kreta, 6986, 0
];

[somacdc]

Thanks for your kind response.

Now, I'm going to slightly change the data for my following desire result.

Customer_Slab	Customer_Name	Customer_Account_no	Customer_Sales	Total_Purchase	result
7	Eric	Boston	450	5000	5000-450=4550
6	Eric	Boston	500	0	4550-500=4050
5	Eric	Boston	320	0	3730
4	Eric	Boston	250	0	3480
3	Eric	Boston	140	0	3340
2	Eric	Boston	659	0	2681
1	Eric	Boston	370	0	2311
7	Monti	Kreta	220	2500	2500-220=2280
6	Monti	Kreta	220	0	2280-220=2060
5	Monti	Kreta	180	0	2060-180=1880
4	Monti	Kreta	840	0	1040
3	Monti	Kreta	1	0	1039
2	Monti	Kreta	456	0	583
1	Monti	Kreta	6986	0	583-6986=0

Please find the attached qvw, which loop is not provide the correct result.

Operations:
LOAD Customer_Slab,
Customer_Name,
Customer_Account_no,
Customer_Sales,
Total_Purchase;

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, 180, 0,0
5, Monti, Kreta, 220, 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
];
//-----Store into qvd
Store Operations into [QVDS\Operations.qvd] (qvd);

DATA:
LOAD MAX(Customer_Slab) AS MAX_SLAB,
SUM(Total_Purchase) AS SUM_Total_Purchase,
Customer_Name,
Customer_Account_no
Resident Operations
Group By Customer_Name,
Customer_Account_no;

DROP Table Operations;

FOR_LOOP_DATA:
LOAD Customer_Name &'-'& Customer_Account_no &'-'& SUM_Total_Purchase &'-'& MAX_SLAB AS CONDITIONS_IDS
Resident DATA;

DROP Table DATA;

FOR EACH vCONDITIONS_IDS IN FieldValueList('CONDITIONS_IDS')

LET vCustomer_Name = subfield('$(vCONDITIONS_IDS)','-',1);
LET vACCOUNT_NUMBER = subfield('$(vCONDITIONS_IDS)','-',2);
LET vTotal_Purchase = subfield('$(vCONDITIONS_IDS)','-',3);
LET vMAX_SLAB = subfield('$(vCONDITIONS_IDS)','-',4);

TOTAL_CASH_BALANCE:
NoConcatenate
LOAD Customer_Name,
Customer_Account_no,
Customer_Sales,
Customer_Slab,
If(Customer_Slab = '$(vMAX_SLAB)', '$(vTotal_Purchase)', 0) AS Total_Purchase,

(Customer_Slab & Customer_Name & Customer_Account_no) AS ORDER
FROM [QVDS\Operations.qvd] (qvd)
WHERE Customer_Name = '$(vCustomer_Name)'
AND Customer_Account_no = '$(vACCOUNT_NUMBER)';

FINAL_OUTPUT:
LOAD Customer_Slab,
Customer_Name,
Customer_Account_no,
Customer_Sales,
Total_Purchase,
RangeMax(RangeSum(Alt(Peek('result'), Total_Purchase), -Rangesum(Customer_Sales)), 0) as result,

IF( (RangeMax(RangeSum(Alt(Peek('result'), Total_Purchase), -Rangesum(Customer_Sales)), 0))>= Customer_Sales, (RangeMax(RangeSum(Alt(Peek('result'), Total_Purchase), -Rangesum(Customer_Sales)), 0)) - Customer_Sales ) AS OUTPUT;;

LOAD Customer_Slab,
Customer_Name,
Customer_Account_no,
Customer_Sales,
Total_Purchase
Resident TOTAL_CASH_BALANCE
Order By ORDER desc;

NEXT vCONDITIONS_IDS

DROP Table FOR_LOOP_DATA;