Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikView forum consolidation is complete. Labels are now required. LEARN ABOUT LABELS
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
jebamalai
Specialist
Specialist

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

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

qualify.jpg

jebamalai
Specialist
Specialist

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.

jebamalai
Specialist
Specialist

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

Qualify *;

unqualify '%*';

Not applicable

Thank you both. I have it working.