Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

pauledrich
Contributor

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

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

Tags (3)
1 Solution

Accepted Solutions
jsaradhi
Valued Contributor

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

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.

4 Replies
jsaradhi
Valued Contributor

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

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.

Not applicable

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

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
Contributor

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

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

pauledrich
Contributor

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

Thanks for the time to reply - all sorted.

Community Browser