Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

suvechha_b
Contributor III

Invalid expression ?

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-

clipboard_image_0.png

 

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.

 

clipboard_image_1.png

 

sample model attached.

1 Solution

Accepted Solutions
asinha1991
Contributor III

Re: Invalid expression ?

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

View solution in original post

14 Replies
asinha1991
Contributor III

Re: Invalid expression ?

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

View solution in original post

anushree1
Valued Contributor II

Re: Invalid expression ?

where is this table getting loaded from   [DWQMV_FINANCIAL_BANK_BALANCE]

suvechha_b
Contributor III

Re: Invalid expression ?

From my DB.

anushree1
Valued Contributor II

Re: Invalid expression ?

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
Contributor III

Re: Invalid expression ?

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
Contributor III

Re: Invalid expression ?

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
Contributor III

Re: Invalid expression ?

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.

clipboard_image_0.png

asinha1991
Contributor III

Re: Invalid expression ?

do these two changes and should work

 

set MAX_STATEMENT_DATE=peek('max_s_date');

 

and use '$(MAX_STATEMENT_DATE)'  with quotes

 

suvechha_b
Contributor III

Re: Invalid expression ?

Hi ,

I am getting max_s_date value as number

clipboard_image_0.png