topic Re: Creating a flag from £'s a Date & Day Of Week in QlikView

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

pauledrich — Tue, 04 Mar 2014 17:05:54 GMT

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.

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

Anonymous — Tue, 04 Mar 2014 17:48:01 GMT

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.

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

Tue, 04 Mar 2014 17:50:29 GMT

Hey Paul,

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

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;

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;

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

pauledrich — Tue, 04 Mar 2014 18:24:44 GMT

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

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

pauledrich — Tue, 04 Mar 2014 18:25:47 GMT

Thanks for the time to reply - all sorted.