topic Re: Dual function () Syntax error in QlikView

Dual function () Syntax error

Wed, 21 Sep 2016 11:59:15 GMT

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

Re: Dual function () Syntax error

sunny_talwar — Wed, 21 Sep 2016 16:15:29 GMT

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;

Re: Dual function () Syntax error

Wed, 21 Sep 2016 17:10:33 GMT

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;

Re: Dual function () Syntax error

sunny_talwar — Wed, 21 Sep 2016 17:30:25 GMT

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;

Re: Dual function () Syntax error

Wed, 21 Sep 2016 18:43:56 GMT

Thanks a million Sunny!

Re: Dual function () Syntax error

sunny_talwar — Wed, 21 Sep 2016 19:01:06 GMT

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