Canonical date problem

[Anonymous]

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!!

[kuczynska]

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;

[#3]

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.

[#4]

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,

[Anonymous]

Hi

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?

[#6]

You need to alias out the other fields.

[Anonymous]

How to do that?

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

[kuczynska]

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

[qlikviewwizard]

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

[Anonymous]

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

1

Other

Validate

SSVD

34

Closed

2

SOP

SS

12345

GMIP

44

SSVD

Open

3

Other

Clean up

SSVD

40

Open

4

SOP

SO

432432

FMIP

43

AQMO

Open

5

SOP

CCC

22145

DMSF

44

AQMO

Open

6

SOP

CCC

432244

DSFF

41

HPMA

Open

7

SOP

SO

65432343

WCJU

34

SSVD

Closed

8

QAP

123321

55

CCC

9

QAP

4234324

44

SO

10

QAP

432432432

33

SO

11

QAP

432434

22

SO

12

QAP

432432

43

SO

13

QAP

432432432

43

SO