LET not working

[LadyBear]

My let statement below is not working.  The variable vSecondLastExtractDate doesn't exist after running the code.  Is there are reason?  For my first attempt I tried Let vSecondLastExtractDate = Date(SecondLastExtractDate,'YYYY-MM-DD');
I thought by adding Max may force the variable to only see one value and make it work.

"Delta Setup2":
LOAD
"Load Process.LD_PRCSS_ID",
"Load Process.EXTRACT_DT"
FROM [lib://SGBGrantsDataQVDProd/GPS/Load Process.qvd](qvd)
where ("Load Process.LD_STATUS_CD" = 'COMPLETED')
;
"Delta Setup1":
First 2 Load
"Load Process.LD_PRCSS_ID" as LD_PRCSS_ID,
"Load Process.EXTRACT_DT" as EXTRACT_DT
Resident "Delta Setup2" Order by "Load Process.LD_PRCSS_ID" desc;
Drop Table "Delta Setup2";

NoConcatenate
"Delta Setup":
First 1 Load EXTRACT_DT as SecondLastExtractDate Resident "Delta Setup1" Order by "LD_PRCSS_ID" asc;
Drop Table "Delta Setup1";

Let vSecondLastExtractDate = Date(Max(SecondLastExtractDate),'YYYY-MM-DD');

[sandeep-singh]

Try this

"Delta Setup2":
LOAD
"Load Process.LD_PRCSS_ID",
"Load Process.EXTRACT_DT"
FROM [lib://SGBGrantsDataQVDProd/GPS/Load Process.qvd](qvd)
where ("Load Process.LD_STATUS_CD" = 'COMPLETED')
;
"Delta Setup1":
First 2 Load
"Load Process.LD_PRCSS_ID" as LD_PRCSS_ID,
"Load Process.EXTRACT_DT" as EXTRACT_DT
Resident "Delta Setup2" Order by "Load Process.LD_PRCSS_ID" desc;
Drop Table "Delta Setup2";

NoConcatenate
"Delta Setup":
First 1 Load EXTRACT_DT as SecondLastExtractDate Resident "Delta Setup1" Order by "LD_PRCSS_ID" asc;
Drop Table "Delta Setup1";

TempTable:

load 
Max(SecondLastExtractDate) as MaxSecondLastExtractDate
Resident "Delta Setup";

Let vSecondLastExtractDate = Date(Peek('MaxSecondLastExtractDate',0,'TempTable'),'YYYY-MM-DD');

Drop table TempTable;

[LadyBear]

I copied your code - Added the missing the Load statement under the TempTable: line.  Now have variable - but there is still no value.  See below.

[sandeep-singh · Accepted solution]

Hi @LadyBear , I have tested the same code and it works completely fine.  Created a dummy script for you to test

Load_process:
Load * inline [
"Load Process.LD_PRCSS_ID","Load Process.EXTRACT_DT","Load Process.LD_STATUS_CD"
21,2020-12-11,COMPLETED
22,2022-04-12,COMPLETED
];

"Delta Setup2":
LOAD
"Load Process.LD_PRCSS_ID",
"Load Process.EXTRACT_DT"
Resident Load_process
where ("Load Process.LD_STATUS_CD" = 'COMPLETED')
;
"Delta Setup1":
First 2 Load
"Load Process.LD_PRCSS_ID" as LD_PRCSS_ID,
"Load Process.EXTRACT_DT" as EXTRACT_DT
Resident "Delta Setup2" Order by "Load Process.LD_PRCSS_ID" desc;
Drop Table "Delta Setup2";

NoConcatenate
"Delta Setup":
First 2 Load EXTRACT_DT as SecondLastExtractDate Resident "Delta Setup1" Order by "LD_PRCSS_ID" asc;
Drop Table "Delta Setup1";

TempTable:
load
Max(SecondLastExtractDate) as MaxSecondLastExtractDate
Resident "Delta Setup";

Let vSecondLastExtractDate = Date(Peek('MaxSecondLastExtractDate',0,'TempTable'),'YYYY-MM-DD');

Drop table TempTable;

exit Script;

[LadyBear]

Dummy script works.  Will retry original script.

[LadyBear]

Working now.    Thank you.