Include Missing Data in Table

[ciaran_mcgowan]

Hi all,

I have an application where annual sales data is displayed in a simple straight table that ranks sales people. Now, my manager wants to see each of the sales figures for each week if they select it. So the annual data looks like this:

Rank	Employee	Total Sales
1	John	1,456,895
2	Paul	1,223,564
3	George	987,123
4	Ringo	402,564

I've edited the script to calculate the accumulative total by week and set that to display if a week/weeks are selected. The only problem is that if an employee doesn't register any sales for a week, they are omitted from the table, and the Rank becomes invalid/incorrect. Is there any way to include them in the table when there is no data? Also, there could be 4/5 week stretches where sales people might not register a sale.

Here is an example of what I'm seeing now. If I select week 24, all users have sales data:

Rank	Employee	Total Sales
1	John	600,234
2	Paul	534,276
3	George	307,659
4	Ringo	125,967

But if I select week 25, not all users have sales data and the table looks like this:

Rank	Employee	Total Sales
1	John	642,896
2	Ringo	142,215

But what I need to see is:

Rank	Employee	Total Sales
1	John	642,896
2	Paul	534,276
3	George	307,659
4	Ringo	142,215

Can this be done?

[sunny_talwar]

Here try this, just tested it out

Table:

LOAD * INLINE [

    Employee, Week, Sales

    A, 24, 100

    A, 26, 120

    B, 24, 120

    B, 25, 130

];

TempTable:

LOAD Distinct Week

Resident Table;

Left Join (TempTable)

LOAD Distinct Employee

Resident Table;

Join (Table)

LOAD *

Resident TempTable;

DROP Table TempTable;

[ciaran_mcgowan]

This still isn't working in my data model but at least I know it's achievable. I'll keep trying and let you know what happens. Without being able to post my app, there's probably not much more you can do.

Thanks for your help.

[sunny_talwar]

Yes you are right, its just difficult to know how might the data model looks and what tweaks are needed.

All the best

Sunny

[ciaran_mcgowan]

No. Each week will contain no data of any kind for some Employees.