topic Re: Include Missing Data in Table in QlikView

Include Missing Data in Table

ciaran_mcgowan — Thu, 20 Oct 2016 09:40:50 GMT

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?

Re: Include Missing Data in Table

sunny_talwar — Thu, 20 Oct 2016 09:42:12 GMT

What are the expression that you have used here?

Re: Include Missing Data in Table

ciaran_mcgowan — Thu, 20 Oct 2016 10:01:32 GMT

Hi Sunny,

I used to use:

Sum({<Month=, Date=>} Sales)

But after creating the weekly accumulation in the script, now I use this on the table:

if(GetSelectedCount(Week) = 1,

Only({<Month=, Week=>} Sales_WeekAccum),

Sum({<Month=, Date=>} Sales)

)

Re: Include Missing Data in Table

prabiradhikary — Thu, 20 Oct 2016 10:03:02 GMT

There is no transaction data for week 25 ?

Re: Include Missing Data in Table

sunny_talwar — Thu, 20 Oct 2016 10:12:21 GMT

So it seems that your script is missing Sales_WeekAccum for period where Sales i not available. May be fix that in the script or for any work around it might be helpful to have a sample to look at

Re: Include Missing Data in Table

ciaran_mcgowan — Thu, 20 Oct 2016 10:17:46 GMT

Hi Sunny,

I'm unable to share any scripting/data on this one. How would it be possible to fix in the script? Inserting dummy rows if a value is missing?

Re: Include Missing Data in Table

sunny_talwar — Thu, 20 Oct 2016 10:21:03 GMT

Make sure accumulation takes place even when sales is 0 in a particular week. You might have to add all weeks for all your Employees before you perform the accumulation. I would try something like this:

Table:

LOAD Employee,

Week

FROM ....;

Join(Table)

LOAD Distinct Week

Resident Table;

and then move forward from there....

Re: Include Missing Data in Table

ciaran_mcgowan — Thu, 20 Oct 2016 10:25:10 GMT

Thanks Sunny, I'll give that a go. Each sales week differs in the amount of weeks so I won't be able to hard code it. I'll trying populating the missing weeks on the fly for each year and let you know how I get on.

Re: Include Missing Data in Table

sunny_talwar — Thu, 20 Oct 2016 10:29:49 GMT

Yes, hard-coding won't be a good solution. I made a small mistake above, try this

Table:

LOAD Employee,

Week

FROM ....;

Join(Table)

LOAD Distinct Week as New_Week

Resident Table;

We want to create a cross join between Employee and all possible Weeks.

Re: Include Missing Data in Table

ciaran_mcgowan — Thu, 20 Oct 2016 14:45:58 GMT

Hi Sunny, I think you are on the right track but this approach isn't feasible here. The above script is only adding each week number to each row.

45 - 50 weeks per year

100 + Employees

Between 0 & 40 sales per week (each sale is it's own row)

The result is too big and won't work. I'll let you know if I make any progress.

Re: Include Missing Data in Table

sunny_talwar — Thu, 20 Oct 2016 14:54:37 GMT

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;

Re: Include Missing Data in Table

ciaran_mcgowan — Thu, 20 Oct 2016 16:31:49 GMT

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.

Re: Include Missing Data in Table

sunny_talwar — Thu, 20 Oct 2016 16:32:53 GMT

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

All the best

Sunny

Re: Include Missing Data in Table

ciaran_mcgowan — Thu, 20 Oct 2016 16:34:50 GMT

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