topic Re: Preceding load on peek() tables in QlikView

Preceding load on peek() tables

Vegar — Sat, 16 Nov 2024 03:49:02 GMT

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;

Re: Preceding load on peek() tables

sunny_talwar — Thu, 18 Apr 2019 11:51:20 GMT

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.

Re: Preceding load on peek() tables

marcus_sommer — Thu, 18 Apr 2019 12:19:46 GMT

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

Re: Preceding load on peek() tables

Gysbert_Wassenaar — Thu, 18 Apr 2019 13:01:27 GMT

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.

Re: Preceding load on peek() tables

Vegar — Thu, 18 Apr 2019 14:00:05 GMT

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.

Re: Preceding load on peek() tables

somacdc — Mon, 19 Aug 2019 12:26:35 GMT

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;

Re: Preceding load on peek() tables

sunny_talwar — Mon, 19 Aug 2019 12:48:12 GMT

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?

Re: Preceding load on peek() tables

somacdc — Mon, 19 Aug 2019 12:57:26 GMT

ohh. Sorry. Please consider 5000 in the inline table

Re: Preceding load on peek() tables

sunny_talwar — Mon, 19 Aug 2019 13:43:46 GMT

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 ];

Re: Preceding load on peek() tables

somacdc — Tue, 20 Aug 2019 07:59:35 GMT

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;

Re: Preceding load on peek() tables

sunny_talwar — Tue, 20 Aug 2019 11:31:20 GMT

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 ];

Re: Preceding load on peek() tables

somacdc — Tue, 20 Aug 2019 12:38:00 GMT

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;

Re: Preceding load on peek() tables

sunny_talwar — Tue, 20 Aug 2019 12:56:54 GMT

Not sure I understand your concern?

Re: Preceding load on peek() tables

somacdc — Tue, 20 Aug 2019 13:04:22 GMT

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.

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;

Re: Preceding load on peek() tables

sunny_talwar — Tue, 20 Aug 2019 13:36:47 GMT

result are disturbed? Can you show an image or a sample? I won't really know what disturbed means unless you provide more details

Re: Preceding load on peek() tables

somacdc — Mon, 26 Aug 2019 12:53:46 GMT

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

Re: Preceding load on peek() tables

sunny_talwar — Mon, 26 Aug 2019 13:01:01 GMT

Where is the qvd?

Re: Preceding load on peek() tables

somacdc — Tue, 27 Aug 2019 06:48:44 GMT

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

Re: Preceding load on peek() tables

somacdc — Tue, 27 Aug 2019 07:40:43 GMT

When open the qvd data, it has multiple record. I'm shocked that how to do it correct

Re: Preceding load on peek() tables

somacdc — Tue, 27 Aug 2019 10:54:51 GMT

I resolved by myself 🙂

Thanks for your support earlier.