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]

date is a dual field in Qlik, if you want to convert number to actual date,

put it under Date() function,

you can put date on top of peek in the variable itself

[suvechha_b]

Hi Asinha,

I did the changes accordingly but still when I am using the condition-

and TransMonth=Month(addmonths('$(MAX_STATEMENT_DATE)',-1) in the flag if statement. I am getting null.

Please kindly assist. sample model attached.

[asinha1991]

I cannot reload and see unless I have your data file

what is the value of this 

Month(addmonths('$(MAX_STATEMENT_DATE)',-1)?

can you try this again?

Month(addmonths($(MAX_STATEMENT_DATE),-1)

[suvechha_b]

Hi,

Q.? Month(addmonths('$(MAX_STATEMENT_DATE)',-1)?

Ans: The value should be 'Aug' as we care currently sitting in 'Sep'

even though I change Month(addmonths($(MAX_STATEMENT_DATE),-1) from Month(addmonths('$(MAX_STATEMENT_DATE)',-1)

I think it is not picking the value of Month($(MAX_STATEMENT_DATE)

FlagFields:
LOAD
RECORD_GM_ID,
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;

[suvechha_b]

it worked.

When I change SET to LET

Let MAX_STATEMENT_DATE=peek('MaxDate');

But my script is showing in black and warning sign-

But the Flag worked and chart.