Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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;
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;
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;
Thanks a million Sunny!
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