topic Re: Trouble with the Lookup... in QlikView

Trouble with the Lookup...

Thu, 07 Sep 2017 04:25:50 GMT

This just ain't working ... I'm getting NULLs in the result.

CREATE TABLE dbo.HadoopIngestionTableCounts

(TableName VARCHAR(128) NOT NULL,

CountRecs BIGINT NOT NULL,

DateIngestion Datetime NOT NULL,

InsertTS Datetime NOT NULL

);

LET vTodayStr = Date(Today());

LET vYesterdayStr = Date(Today() - 1);

LastRunStatistics:

SQL SELECT CountRecs FROM dbo.HadoopIngestionTableCounts WHERE TableName IN ('customers', 'suppliers') AND DateIngestion = '$(vYesterdayStr)';

LET vNumRowsMemDemoLast = Lookup('CountRecs', 'TableName', 'customers', 'LastRunStatistics');

LET vNumRowsMembershipLast = Lookup('CountRecs', 'TableName', 'suppliers', 'LastRunStatistics');

Re: Trouble with the Lookup...

Anil_Babu_Samineni — Thu, 07 Sep 2017 15:56:03 GMT

Variables should placed before Load / Select statement only..

Re: Trouble with the Lookup...

antoniotiman — Thu, 07 Sep 2017 16:07:31 GMT

Hi Romi,

where is 'TableName' Field in SQL Select Statement ?

Re: Trouble with the Lookup...

Thu, 07 Sep 2017 16:49:11 GMT

Ha ... Thanks so much for pointing out the silly error. It's working all good now. Changed script is as follows...

CREATE TABLE dbo.HadoopIngestionTableCounts

(TableName VARCHAR(128) NOT NULL,

CountRecs BIGINT NOT NULL,

DateIngestion Datetime NOT NULL,

InsertTS Datetime NOT NULL

);

LET vTodayStr = Date(Today());

LET vYesterdayStr = Date(Today() - 1);

LastRunStatistics:

SQL SELECT TableName, CountRecs FROM dbo.HadoopIngestionTableCounts WHERE TableName IN ('customers', 'suppliers') AND DateIngestion = '$(vYesterdayStr)';

LET vNumRowsMemDemoLast = Lookup('CountRecs', 'TableName', 'customers', 'LastRunStatistics');

LET vNumRowsMembershipLast = Lookup('CountRecs', 'TableName', 'suppliers', 'LastRunStatistics');