Creating a flag from £'s a Date & Day Of Week

[pauledrich]

Thanks for any help on this problem I have as I am completely at a loss.

I want to create a flag 'Compliance' and it would be 'Pass' or 'Fail'.

Table1 has the field flag   'MinimumPosition'  which is 'over' and 'Under'  and Table 2 has a spreadsheet with BranchID, CollectionDay and Frequency - it has no dates.

I have an expression which works fine in a straight table, with some calculated dimensions - the expression I want to emulate in the script is    =If(WeekDay = CollectionDay and MinimumPosition ='Over', 'Fail','Pass').

In the script I have  on a tab after the MasterCalendar :-

tmp:

Load Distinct

EODID,

BranchEodID,

MinimumPosition

Resident Table1;

Left Join (Table1)

Load Distinct

BranchNo as BranchEodID,

CollectionDay as DowCollection

Resident Table2:

Drop Table tmp;

Left Join (Table1)

load

EODID,

DOWCollection,

MinimumPosition,

SummaryDate,

If(MinimumPosition = 'Over' and DOWCollection = Date(WeekDay(SummaryDate),'DD/MM/YYYY'), 'Fail','Pass') as Compliance_Flag

Resident (Table1);

I have also tried various combinations of the above with no success and I just cant see where I am going wrong - Removing the ' ' from Over has a Field not found message pop up.

Thanks again for help.

P

[Anonymous · Accepted solution]

sounds like DOWCollection is a day (Sun, Mon, etc.) and Date() returns a date in DD/MM/YYY format and so your if condition always returns 'Pass'.

you probably are looking for just weekday(SummaryDate), so removing date function may work.

[#3]

Hey Paul,

Can you try like this(Please make sure the fields listed below are available in the respective tables):

A:

Load Distinct

EODID,

BranchEodID,

MinimumPosition,

SummaryDate

Resident Table1;

Left Join (A)

Load Distinct

BranchNo as BranchEodID,

CollectionDay as DowCollection

Resident Table2:

Rename Table A to Temp;

A:

Noconcatenate

Load Distinct

EODID,

BranchEodID,

MinimumPosition,

SummaryDate,

DowCollection,

If(MinimumPosition = 'Over' and DOWCollection = Date(WeekDay(SummaryDate),'DD/MM/YYYY'), 'Fail','Pass') as Compliance_Flag

Resident Temp;

Drop Table Temp;

[pauledrich]

Thank you - this didn't work as  it created a few synthetic keys in my data model - thank you for your time.

[pauledrich]

Thanks for the time to reply - all sorted.