topic Re: Generating Missing Dates for Facts Table in QlikView

Generating Missing Dates for Facts Table

Mon, 08 Dec 2014 14:16:46 GMT

Hi,

I have a facts table that contains snapshots for both weeks and dates throughout the week. My data refers to the prices for part numbers and I have discovered that there there is not a price available for every day in my data. As I have discovered that there is no snapshot date for the start of some weeks, my calculations for another project do not take place and values are missing. I have tried to use some established methods involving the IntervalMatch function and the AutoGenerate feature to create data for these omitted rows, with little success so far.

My question is, how do I connect my custom date range field to my established Price table to allow prices to be available for every day in my date range?

For further reference - I have attached my test QVW file for reviewal.

Thanks,

Leigh

Re: Generating Missing Dates for Facts Table

stevedark — Tue, 06 Jan 2015 00:51:44 GMT

Hi Leigh,

This blog post by hic gives an excellent example of populating missing values by doing a loop:

How to populate a sparsely populated field

It's worth reading the comments on the post too for more background on the technique.

Steve

Re: Generating Missing Dates for Facts Table

Tue, 06 Jan 2015 10:23:19 GMT

Hi Steve,

This is exactly what I was looking for. Thanks!

Regards,

Leigh

Re: Generating Missing Dates for Facts Table

engishfaque — Tue, 06 Jan 2015 11:28:22 GMT

Dear Leigh,

Missing date can be generate by using "File Wizard: Transform" > Tab "Fill".

Here are the steps,

Edit Script > Browser "Table Files" > File Wazard : "Transform" > Enable Transformation Step > Tab "Fill" > Fill > Apply Specification | Apply Condition | Fill type > OK > Finsih.

Kind regards,

Ishfaque Ahmed