Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Synthetic error need to create Key table with Join

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.

Qlik_syn_error.jpg

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

//    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]);

//

6 Replies
Anonymous
Not applicable
Author

Try with Qualify

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

And then use the below logic

Qualify *;

unqualify '%*';

Not applicable
Author

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

qualify.jpg

Anonymous
Not applicable
Author

should be before the table where the synthetic keys start forming

vishsaggi
Champion III
Champion III

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,

V.

Anonymous
Not applicable
Author

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

Qualify *;

unqualify '%*';

Not applicable
Author

Thank you both. I have it working.