Dual function () Syntax error

[#1]

All,

I have rewritten the dual statement many of times because of the syntax error before the first 'dual' statement. Goal is to subtract todays date from the max date from a group of orders. If the number is less then Allowed Days Between then a 1 is return. If the number is greater then Allowed Days Between return a 0. Please help

LinkTableFlag:

Load
[Actual Finish] AS [Max Finish]
Resident Orders;

Concatenate(LinkTableFlag)
Load
[Allowed Days Between]
Resident PLAND;

if(today() - Date([Max Finish]) <= [Allowed Days Between] , Dual(‘Y’,1),Dual(‘N’,0)) as CompliantFlag

[sunny_talwar]

This if statement is outside of any load statement? You probably need this

LinkTableFlag:

LOAD [Actual Finish] AS [Max Finish]
Resident Orders;

Join (LinkTableFlag)
Load [Allowed Days Between]
Resident PLAND;

FinalLinkTableFlag

LOAD [Allowed Days Between],

          [Max Finish],

          If(Today() - [Max Finish] <= [Allowed Days Between] , Dual(‘Y’,1),Dual(‘N’,0)) as CompliantFlag

Resident LinkTableFlag;

DROP Table LinkTableFlag;

[#3]

I changed  the coding to as shown below. The only issue I am having is with the MAX(Actual Finish) as MaxFinish.

If I removed Max from Actual Finish the code will work fine. I need the Max date value from the actual finish data field in order to display the most current date.

LinkTableFlagTemp:
noconcatenate
Load
MaintItem ,
[Allowed Days Between]
Resident PLAND;

Left Join (LinkTableFlagTemp)
Load
MaintItem ,
Max([Actual Finish]) as MaxFinish
Resident Orders;

LinkTableFlag:
NoConcatenate
load*,
if(today() - Date(MaxFinish) <= [Allowed Days Between] ,Dual('Y',1), Dual('N',0)) as ComplaintFlag
Resident LinkTableFlagTemp;

Drop Table LinkTableFlagTemp;

[sunny_talwar · Accepted solution]

Do you want max for each MaintItem. If yes, then try this:

LinkTableFlagTemp:
NoConcatenate
LOAD MaintItem ,
     [Allowed Days Between]
Resident PLAND;

Left Join (LinkTableFlagTemp)
LOAD MaintItem ,
     Max([Actual Finish]) as MaxFinish
Resident Orders

Group By MaintItem;

LinkTableFlag:
NoConcatenate
LOAD *,
     If(Today() - Date(MaxFinish) <= [Allowed Days Between] ,Dual('Y',1), Dual('N',0)) as ComplaintFlag
Resident LinkTableFlagTemp;

Drop Table LinkTableFlagTemp;

[#5]

Thanks a million Sunny!

[sunny_talwar]

Not a problem. If you got what you were looking for, I would suggest you close this thread by marking correct and helpful responses.

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny