Invalid expression ?

[suvechha_b]

Hi All ,

Please kindly have a look on the below script-

"FINANCE":

LOAD

       [RECORD_GRP_ID]

      ,[RECORD_GM_ID]

      ,[RECORD_BATCH_GRP_ID]

      ,[RECORD_BATCH_GM_ID]

      , [Bank Name]

      ,[Bank Account name]

      ,[Bank Account]

      , [Statement Date]

      ,Day([Statement Date]) as TransDay

      ,Month([Statement Date]) as TransMonth

      ,Year([Statement Date]) as TransYear

      ,Week([Statement Date]) as TransWeek

      , [Opening Balance]

      , [Closing Balance]

      , [Amt Credit Trx]

      , [Amt Debit Trx]

      , [Type of Account]

      , [GL Account]

      , [Company Code];

SELECT [RECORD_GRP_ID]

      ,[RECORD_GM_ID]

      ,[RECORD_BATCH_GRP_ID]

      ,[RECORD_BATCH_GM_ID]

      ,[BANK_NAME] as [Bank Name]

      ,[BANK_ACCOUNT_HOLDER_NAME] as [Bank Account name]

      ,[BANK_ACCOUNT_NUMBER] as [Bank Account]

      ,[STATEMENT_DATE] as [Statement Date]   

      ,[OPENING_BALANCE] as [Opening Balance]

      ,[CLOSING_BALANCE] as [Closing Balance]

      ,[AMT_CREDIT_TRX] as [Amt Credit Trx]

      ,[AMT_DEBIT_TRX] as [Amt Debit Trx]

      ,[TYPE_OF_ACCOUNT] as [Type of Account]

      ,[GL_ACCOUNT] as [GL Account]

      ,[COMPANY_CODE] as [Company Code]

  FROM [DWQMV_FINANCIAL_BANK_BALANCE];

   "FlagFields":

  LOAD

  RECORD_GM_ID,

         if([TransDay] >=1 and [TransDay] <=31 and TransMonth=Month(addmonths(Max([Statement Date]),-1) ), 'Opening Bal',

           // if([TransDay] >=1 and [TransDay] <=7 and TransMonth=Month(Max(TRANSACTION_DATE) ), '1-7'& Month(Max(TRANSACTION_DATE) ) &''',

           if([TransDay] >=1 and [TransDay] <=7 and TransMonth=Month(Max([Statement Date]) ), '1-7Sep',

             if([TransDay] >=8 and [TransDay] <=14 and TransMonth=Month(Max([Statement Date]) ), '8-14Sep',

              if([TransDay] >=15 and [TransDay] <=21 and TransMonth=Month(Max([Statement Date]) ), '15-21Sep',

               if([TransDay] >=22 and [TransDay] <=28 and TransMonth=Month(Max([Statement Date]) ), '22-28Sep')))) ) as FlagData

 Resident

 FINANCE;

 left join (FINANCE)

 Load

 RECORD_GM_ID,

 FlagData

 Resident FlagFields;

 drop Table FlagFields;

and getting an error in the "Flagfields" section with invalid expression-

Please if any one can help.

My req- is to do a report like below- to show the Opening Balance per week of current month and full previous month for companycode.

sample model attached.

[asinha1991 · Accepted solution]

you cannot Max([Statement Date] without group by, take this in variable using peek and then evaluate, something like this

MAX_DATE:

LOAD 

max(STATEMENT_DATE) as max_s_date,

max(TRANSACTION_DATE) as max_t_date

RESIDENT FINANCE;

set MAX_STATEMENT_DATE=peek('max_s_date',1,'MAX_DATE');

set MAX_TRANSACTION_DATE=peek('max_t_date',1,'MAX_DATE');

and replace all

max(statementdate) with $(MAX_STATEMENT_DATE) and max(transaction) with $(MAX_TRANSACTION_DATE)

if you want current day, you can use today() instead

and replace

[anushree1]

where is this table getting loaded from   [DWQMV_FINANCIAL_BANK_BALANCE]

[suvechha_b]

From my DB.

[anushree1]

Could you place an exit script statement after   FROM [DWQMV_FINANCIAL_BANK_BALANCE];

and check if the data load gets done till there, I think its in this place its failing .

Have you created the Lib Statement for Connection in Qlik sense

[suvechha_b]

It ran through after I put the exit script after the table gets [DWQMV_FINANCIAL_BANK_BALANCE] loaded.

 I think my script is wrong in below script.

"FlagFields":
LOAD
RECORD_GM_ID,
if([TransDay] >=1 and [TransDay] <=31 and TransMonth=Month(addmonths(Max([Statement Date]),-1) ), 'Opening Bal',
// if([TransDay] >=1 and [TransDay] <=7 and TransMonth=Month(Max(TRANSACTION_DATE) ), '1-7'& Month(Max(TRANSACTION_DATE) ) &''',
if([TransDay] >=1 and [TransDay] <=7 and TransMonth=Month(Max([Statement Date]) ), '1-7Sep',
if([TransDay] >=8 and [TransDay] <=14 and TransMonth=Month(Max([Statement Date]) ), '8-14Sep',
if([TransDay] >=15 and [TransDay] <=21 and TransMonth=Month(Max([Statement Date]) ), '15-21Sep',
if([TransDay] >=22 and [TransDay] <=28 and TransMonth=Month(Max([Statement Date]) ), '22-28Sep')))) ) as FlagData
Resident
FINANCE;

left join (FINANCE)
Load
RECORD_GM_ID,
FlagData
Resident FlagFields;

drop Table FlagFields;

[asinha1991]

It is because you are aggregating (max) without using group by clause

you cannot max something along with other fields without using group by, think from sql perspective,

can you do select ID,max(date) from table? it has to be select ID, max(date) from table group by ID.

you need to use variables as I suggested

[suvechha_b]

Hi Asinha,

I did your script, though it loaded without error. But my FlagData is coming null. Will you please help me to get it correct.

I am uploading the sample model.

[asinha1991]

do these two changes and should work

set MAX_STATEMENT_DATE=peek('max_s_date');

and use '$(MAX_STATEMENT_DATE)'  with quotes

[suvechha_b]

Hi ,

I am getting max_s_date value as number