topic Re: Canonical date problem in QlikView

Canonical date problem

Anonymous — Tue, 18 Aug 2015 06:28:31 GMT

hi all,

I have normally have 3 xls sheets with different tasks, these I would like to combine into 1 MasterFile and use QlikView to present the data.

As the 3 xls have their own date I'm looking at the CanonicalDate, but its not easy to understand fully

My load script look like this

SET ThousandSep='.';

SET DecimalSep=',';

SET MoneyThousandSep='.';

SET MoneyDecimalSep=',';

SET MoneyFormat='kr. #.##0,00;kr. -#.##0,00';

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD-MM-YYYY';

SET TimestampFormat='DD-MM-YYYY hh:mm:ss[.fff]';

SET MonthNames='jan;feb;mar;apr;maj;jun;jul;aug;sep;okt;nov;dec';

SET DayNames='ma;ti;on;to;fr;lø;sø';

TempSource:

LOAD ID as TaskID,

Type,

[QA Responsible],

[QBIQ no./other description],

[Present in GS SOP Process Overview Excel file],

[QBIQ doc.title/other description],

[SOP author/ contact person],

[GS Area],

[Project

name],

[CR no.],

[Triggered by NC3/QAP/Process update],

[QAP deadline if relevant],

Week([1. review deadline if relevant]) as WeekSOP1Review,

Week([2. review deadline if relevant]) as WeekSOP2Review,

[Expected date available on QBIQ if relevant],

MakeWeekDate(2015,[Week of expected QA]) as WeekNumberExpectedQA,

[Date of expected QA (if any)],

[OPEN/ FINALISED],

[Actual date of finalising all QA activities],

QBIQ,

Area,

[Document title],

[lastest effective date in QBIQ],

[Current author],

[Project name],

Task,

[Expected startdate],

[Expected end date]

FROM

MasterFileTasks.xlsx

(ooxml, embedded labels, table is Upcoming);

Qualify * ; // to avoid syn keys

SOPS:

LOAD

TaskID,

[OPEN/ FINALISED],

[QBIQ doc.title/other description],

[QA Responsible],

[Area]

Resident TempSource where Type ='SOP';

QAPS:

LOAD

TaskID,

QBIQ,

Area,

[Document title],

Week([lastest effective date in QBIQ]) as Weeknumber,

[Current author]

Resident TempSource where Type ='QAP';

PROJECTS:

LOAD

TaskID,

[Project name],

Task,

[Expected startdate],

[Expected end date]

Resident TempSource where Type ='Projects';

DateBridge:

Load TaskID, Applymap('TaskID2TaskDate',TaskID,Null()) as CanonicalDate, 'Tasks' as DateType

Resident TempSource;

When I look at the table viewer it looks like this

I was expecting some connection to the databridge!!

The overall idea is that I select 1 date from a mastercalender and then I can see which tasks we have pending for the given week!!

Re: Canonical date problem

kuczynska — Tue, 18 Aug 2015 06:48:49 GMT

You should start from using UNQUALIFY fieldname; in your script. Right now there aren't any association between the tables as all of the fields have different names (caused by QUALIFY *;)

How exactly you want to associate your tables? Via date field? or TaskID? For example in the PROJECTS table you have 2 date fields - which one you would want to use?

Maybe start from using UNQUALIFY TaskID;

Re: Canonical date problem

Tue, 18 Aug 2015 06:50:40 GMT

You must use:

UNQUALIFY <field name>

In order to create a the desired links.

QUALIFY * adds the name of the table before all field names unless you UNQUALIFY the required fields by name.

Re: Canonical date problem

Tue, 18 Aug 2015 07:05:30 GMT

Remove the QUALIFY * statement as you don't need it.

TASKID is your link between tables and databridge, to avoid synthetic keys, alias the other fields that are common across the tables,

Re: Canonical date problem

Anonymous — Tue, 18 Aug 2015 07:11:53 GMT

I have removed the Qualify statement and now it looks like this

It seems that TaskID is at least correct for the DataBridge but the rest got a $Syn, why is that and why was it not the TaskID that will be the connection?

Re: Canonical date problem

Tue, 18 Aug 2015 07:14:33 GMT

You need to alias out the other fields.

Re: Canonical date problem

Anonymous — Tue, 18 Aug 2015 07:17:32 GMT

How to do that?

I expect that if I e.g. remove the SOPS: then the alias will be removed but table also removed !!

Re: Canonical date problem

kuczynska — Tue, 18 Aug 2015 07:20:14 GMT

You must rename the fields creating additional associations between the tables - either manually by creating an alias using AS statement in your load script, or you can keep QUALIFY/UNQUALIFY functions in your code.

What I would do now is too keep :

QUALIFY*; // in your load script

//and also add the line:

UNQUALIFY TaskID;

In that way you can keep the full control of your model - if you think there should be more links (or different links) between the tables - simply add more fieldnames to the UNQUALIFY statement, example:

UNQUALIFY TaskID, DateType; //and so on

Re: Canonical date problem

qlikviewwizard — Tue, 18 Aug 2015 07:39:22 GMT

Can you attach the sample data. It will help the folks to better understand the requirement.

Re: Canonical date problem

Anonymous — Tue, 18 Aug 2015 08:30:02 GMT

I have made a bit more simple example.....

The goal to have 1 masterfile that will be used to list all the tasks for a specific week within the different areas (Other, SOP and QAP)

How can I attach the example? I can only add a video or image!!!

My script look like this

SET ThousandSep='.';

SET DecimalSep=',';

SET MoneyThousandSep='.';

SET MoneyDecimalSep=',';

SET MoneyFormat='kr. #.##0,00;kr. -#.##0,00';

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD-MM-YYYY';

SET TimestampFormat='DD-MM-YYYY hh:mm:ss[.fff]';

SET MonthNames='jan;feb;mar;apr;maj;jun;jul;aug;sep;okt;nov;dec';

SET DayNames='ma;ti;on;to;fr;lø;sø';

TempSource:

LOAD TaskID,

Type,

[Other Task],

[Other Task QA Responsible],

[Other Task Expected QA Work],

[Other Task State],

SOPArea,

[SOP Number],

[SOP Author],

[SOP Expected QA Work],

[SOP QA Responsible],

[SOP Status],

[QAP QBIQ Number],

[QAP Deadline],

[QAP Area]

FROM

MasterFileTest.xlsx

(ooxml, embedded labels, table is Sheet1);

Qualify*; // to avoid syn keys

UnQualify TaskID;

SOPS:

LOAD

TaskID,

SOPArea,

[SOP Number],

[SOP Author],

[SOP Expected QA Work],

[SOP QA Responsible],

[SOP Status]

Resident TempSource where Type ='SOP';

QAPS:

LOAD

TaskID,

[QAP QBIQ Number],

[QAP Deadline],

[QAP Area]

Resident TempSource where Type ='QAP';

OTHER:

LOAD

TaskID,

[Other Task],

[Other Task QA Responsible],

[Other Task Expected QA Work],

[Other Task State]

Resident TempSource where Type ='Other';

DateBridge:

Load TaskID, Applymap('SOP Expected QA Work2TaskDate',TaskID,Null()) as CanonicalDate, 'SOP' as DateType

Resident TempSource;

Load TaskID, Applymap('QAP Deadline2TaskDate',TaskID,Null()) as CanonicalDate, 'QAP' as DateType

Resident TempSource;

Load TaskID, Applymap('Other Task Expected QA Work2TaskDate',TaskID,Null()) as CanonicalDate, 'Other' as DateType

Resident TempSource;

//Drop Table TempSource;

And my xls looks like this

TaskID

Type

Other Task

Other Task QA Responsible

Other Task Expected QA Work

Other Task State

SOPArea

SOP Number

SOP Author

SOP Expected QA Work

SOP QA Responsible

SOP Status

QAP QBIQ Number

QAP Deadline

QAP Area

Other

Validate

SSVD

Closed

SOP

12345

GMIP

SSVD

Open

Other

Clean up

SSVD

Open

SOP

432432

FMIP

AQMO

Open

SOP

CCC

22145

DMSF

AQMO

Open

SOP

CCC

432244

DSFF

HPMA

Open

SOP

65432343

WCJU

SSVD

Closed

QAP

123321

CCC

QAP

4234324

QAP

432432432

QAP

432434

QAP

432432

QAP

432432432