Synthetic error need to create Key table with Join

Mon, 15 Jun 2015 14:59:18 GMT

I am hoping I am posting in the right group and that someone can help. I am a newbie trying to setup my first data model and it is not going well. There were a bunch or $Syn errors. (see graphic) So I know I tried to create a Key table and then Join the others to it but the result below leads to even more errors. Can someone point me in the right direction.

//********************

// Q1 & Q2 Schedule

//********************

Schedule:

LOAD [Class ID],

[Course Title],

[Scheduled Class Start Date],

[Scheduled Class End Date],

[Scheduled Class Location],

[Class Delivery Name],

[Base Class Price],

Region,

[Class Status],

Special_Request,

[Instructor Full Name],

[Open Seats],

[Max Count],

Flag

FROM

[2015.05.22-ILT_Fill_Rate_with_Registrations.xlsx]

(ooxml, embedded labels, table is [Q1-2015]);

//**********************

// Key Table

//**********************

Key:

LOAD Distinct

[Class ID],

[Course Title],

[Scheduled Class Start Date],

[Scheduled Class End Date],

[Scheduled Class Location],

[Class Delivery Name],

[Base Class Price],

Resident Schedule;

Join (Key)

LOAD Distinct

[Class ID],

[Course Title],

[Scheduled Class Start Date],

[Scheduled Class End Date],

[Scheduled Class Location],

[Class Delivery Name],

[Base Class Price],

Region,

[Class Status],

Special_Request,

[Instructor Full Name],

[Open Seats],

[Max Count],

Flag

FROM

[2015.05.22-ILT_Fill_Rate_with_Registrations.xlsx]

(ooxml, embedded labels, table is [Q2-2015]);

// Online labs

Join (Key)

LOAD Distinct

[Class ID],

[Course Title],

[Scheduled Class Start Date],

[Scheduled Class End Date],

[Scheduled Class Location],

[Class Delivery Name],

[Base Class Price],

[Class Region],

[Open Seats for Scheduled Class],

[Scheduled Class Max Count]

FROM

[2015.05.22-ILT_Fill_Rate_with_Registrations.xlsx]

(ooxml, embedded labels, table is [Online Labs]);

//// Q1 Cancellations

//LOAD [Class ID],

// [Course Title],

// [Scheduled Class Start Date],

// [Scheduled Class End Date],

// [Scheduled Class Location],

// [Class Delivery Name],

// [Base Class Price],

// Region,

// Status,

// Special_Request,

// F11,

// [Open Seats],

// [Max Count],

// Flag

//FROM

//[2015.05.22-ILT_Fill_Rate_with_Registrations.xlsx]

//(ooxml, embedded labels, table is [Q1-Cancellations]);

//// Q2 Cancellation

//LOAD [Class ID],

// [Course Title],

// [Scheduled Class Start Date],

// [Scheduled Class End Date],

// [Scheduled Class Location],

// [Class Delivery Name],

// [Base Class Price],

// Region,

// Status,

// Special_Request,

// F11,

// [Open Seats],

// [Max Count],

// Flag

//FROM

//[2015.05.22-ILT_Fill_Rate_with_Registrations.xlsx]

//(ooxml, embedded labels, table is [Q2-Cancellations]);

////********************

//// Q1 & Q2 Registrations

////********************

//Registration:

//LOAD [Class ID],

// [Class Name],

// [Person Organization Name],

// [Person Full Name],

// [Person E-mail],

// [Registration Status],

// [Order Item Approval Status],

// [Registration Date],

// [Scheduled Class Start Date],

// [Scheduled Class Location],

// [Person Domain],

// Region

//FROM

//[2015.05.22-ILT_Fill_Rate_with_Registrations.xlsx]

//(ooxml, embedded labels, table is [Q1-Registration]);

//LOAD [Class ID],

// [Class Name],

// [Person Organization Name],

// [Person Full Name],

// [Person E-mail],

// [Registration Status],

// [Order Item Approval Status],

// [Registration Date],

// [Scheduled Class Start Date],

// [Scheduled Class Location],

// [Person Domain],

// Region

//FROM

//[2015.05.22-ILT_Fill_Rate_with_Registrations.xlsx]

//(ooxml, embedded labels, table is [Q2-Registrations]);

Re: Synthetic error need to create Key table with Join

Anonymous — Mon, 15 Jun 2015 15:15:07 GMT

Try with Qualify

Rename your key fields to start with the "%" .ie [class is] as %ClassID

And then use the below logic

Qualify *;

unqualify '%*';

Re: Synthetic error need to create Key table with Join

Mon, 15 Jun 2015 15:28:45 GMT

Jebamalai, Where exactly should I place the qualify and unqualify statements?

Re: Synthetic error need to create Key table with Join

Anonymous — Mon, 15 Jun 2015 15:32:34 GMT

should be before the table where the synthetic keys start forming

Re: Synthetic error need to create Key table with Join

vishsaggi — Mon, 15 Jun 2015 15:42:07 GMT

Hello Tricia,

You have to use Qualify and Unqualify like below:

For eg:

// Online labs

QUALIFY *;

Join (Key)

LOAD Distinct

[Class ID],

[Course Title],

[Scheduled Class Start Date],

[Scheduled Class End Date],

[Scheduled Class Location],

[Class Delivery Name],

[Base Class Price],

[Class Region],

[Open Seats for Scheduled Class],

[Scheduled Class Max Count]

FROM

[2015.05.22-ILT_Fill_Rate_with_Registrations.xlsx]

(ooxml, embedded labels, table is [Online Labs]);

UNQUALIFY [Class ID], [Course Title] <Like how many cols you want to Unqualify list> ;

Regards,

Re: Synthetic error need to create Key table with Join

Anonymous — Mon, 15 Jun 2015 15:48:00 GMT

put the below code before the key table and make sure you rename your Class ID field to %ClassId

Qualify *;

unqualify '%*';

Re: Synthetic error need to create Key table with Join

Mon, 15 Jun 2015 19:13:59 GMT

Thank you both. I have it working.

topic Re: Synthetic error need to create Key table with Join in QlikView

Synthetic error need to create Key table with Join

Re: Synthetic error need to create Key table with Join

Re: Synthetic error need to create Key table with Join

Re: Synthetic error need to create Key table with Join

Re: Synthetic error need to create Key table with Join

Re: Synthetic error need to create Key table with Join

Re: Synthetic error need to create Key table with Join