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: 
fabdulazeez
Partner - Creator III
Partner - Creator III

help with DataModel

 I have 2 tables.

1 with schedule for the salesman 

smcodedaycustcode
101Sundayxcdddd
101Sundayxcddee

 

2 with actual route

smcodedaydatecustcode
101Sunday23-Junxcdddd
101Monday24-Junxcsssss

 

 

Salesman can go to any customer. What will be the best way to join the tables.

 When i select date i need to know

1)customers not visited

2) Customers visited extra

3)Customer visited as per schedule.

1 Solution

Accepted Solutions
JordyWegman
Partner - Master
Partner - Master

Hi,

The best thing would be to create two indicators. One for the actual appointments and one for the 'extra' (2nd table). Call them: _indActualAppointments and give 1 if actual. The other one call _indExtraApointments and give also the 1 if this is right.

Then make the formula:

Count({$< _indActualAppointments  = {0}, _indExtraApointments {1}>}smcode)

This will give you all the appointments that were extra.

How do you retrieve from the data that he missed something?

Jordy

Climber 

Work smarter, not harder

View solution in original post

10 Replies
JordyWegman
Partner - Master
Partner - Master

Hi Fawazeez,

Create a concatenated key based on smcode, day and custcode:

smcode &'|'& day &'|'& custcode as %Key

Do this for both tables and join based on this field. Make sure that the other fields are names based on the table so you know where this information is coming from. 

Jordy

Climber 

Work smarter, not harder
fabdulazeez
Partner - Creator III
Partner - Creator III
Author

Thanks for the reply. I have made a join like that.  Please help me derive below values.

When i select a date

i need to know which all customers he visited not in route.

and which all customers he missed.

JordyWegman
Partner - Master
Partner - Master

Hi,

The best thing would be to create two indicators. One for the actual appointments and one for the 'extra' (2nd table). Call them: _indActualAppointments and give 1 if actual. The other one call _indExtraApointments and give also the 1 if this is right.

Then make the formula:

Count({$< _indActualAppointments  = {0}, _indExtraApointments {1}>}smcode)

This will give you all the appointments that were extra.

How do you retrieve from the data that he missed something?

Jordy

Climber 

Work smarter, not harder
fabdulazeez
Partner - Creator III
Partner - Creator III
Author

Sorry for not being very clear.

two tables are his 1)schedule and 2) actuals.

 When i select a date the extra visited will not be available as those customers are not inside the route

 

Missing ones are those in the schedule  which are not in actuals.

JordyWegman
Partner - Master
Partner - Master

No problem, but two questions first. Did you make a join, like a left/inner join? Or did you link the two tables? 

This will have a bit impact on the rest of the solution.

Jordy

Climber

Work smarter, not harder
fabdulazeez
Partner - Creator III
Partner - Creator III
Author

I made only association. No joins

JordyWegman
Partner - Master
Partner - Master

Try to make a join and create the two indicators. This will make your statements way easier to set up.

Jordy

Climber

Work smarter, not harder
JordyWegman
Partner - Master
Partner - Master

Did it help to make joins?

Jordy

Climber

Work smarter, not harder
fabdulazeez
Partner - Creator III
Partner - Creator III
Author

I tried OUTER JOIN on the 3 fields . But i couldn't get the relation correct.

when we select date, it is available only in actuals table, so to get number of planned customers gets difficult from relation.