help with DataModel

[fabdulazeez]

 I have 2 tables.

1 with schedule for the salesman 

smcode	day	custcode
101	Sunday	xcdddd
101	Sunday	xcddee

2 with actual route

smcode	day	date	custcode
101	Sunday	23-Jun	xcdddd
101	Monday	24-Jun	xcsssss

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.

[JordyWegman]

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 

[fabdulazeez]

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 · Accepted solution]

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 

[fabdulazeez]

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]

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

[fabdulazeez]

I made only association. No joins

[JordyWegman]

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

Jordy

Climber

[JordyWegman]

Did it help to make joins?

Jordy

Climber

[fabdulazeez]

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.