- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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-
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.
- « Previous Replies
-
- 1
- 2
- Next Replies »
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
where is this table getting loaded from [DWQMV_FINANCIAL_BANK_BALANCE]
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
From my DB.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
do these two changes and should work
set MAX_STATEMENT_DATE=peek('max_s_date');
and use '$(MAX_STATEMENT_DATE)' with quotes
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi ,
I am getting max_s_date value as number
- « Previous Replies
-
- 1
- 2
- Next Replies »