Solved: Re: Preceding load on peek() tables - Page 2 - Qlik Community

Vegar · ‎2019-04-18

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;

Vegar
Qlik Community MVP

sunny_talwar · ‎2019-08-20

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

somacdc · ‎2019-08-20

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;

sunny_talwar · ‎2019-08-20

Not sure I understand your concern?

somacdc · ‎2019-08-20

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;

sunny_talwar · ‎2019-08-20

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

somacdc · ‎2019-08-26

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

sunny_talwar · ‎2019-08-26

Where is the qvd?

somacdc · ‎2019-08-27

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

somacdc · ‎2019-08-27

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

somacdc · ‎2019-08-27

I resolved by myself 🙂

Thanks for your support earlier.

Preceding load on peek() tables

Peek

preceding load

Qlik Sense

QlikView

Scripting