Dates with Zero Values. QV8 vs QV11

[lfalmoguera]

Hi guys,

Maybe this question has been answered previously but I can´t find any solution that suits my problem in a propper way.

I have a huge DB with information organized by days and many oher columns. But there are some scenarios (combination of Date and many other fields) with no values. Therefore, for those scenarios with no values, I want to represent them as cero.

I want to show them as a linear graphic with zero values when approppiate.

Imagine I have:

01/01/2012 > 10

03/01/2012 > 15

I want the line to go from 10 down to 0 (for 02/01/2012) and then rise to 15.

I have made a "master calendar" and it works in QV8... but I can´t make it work in QV11 using the exact same code.

It seems the new version has something against "null fields". Or maybe a I have to add something in my calendar.

Please, find attached an easy sample, and both files in QV8 and QV11 (where lines do not hit the zero value when appropiatte).

Thanks a lot in advance!

[swuehl]

I don't have much experience in QV8 and no version installed, so I can't check the behaviour of the old version against the new.

But anyway, I think you want to get a working solution for your requirement in QV11 and that's what I tried.

I don't think the problem are NULLs in fields in general, but if you don't provide a full set of combinations for your dimensions.

I don't think there is just a property to change the behaviour back to what you expect and see in QV8, but there might be some work arounds (including changes to data model / expressions):

a) You could create a data island in your script and use this as dimension and use a conditional in your expression to check for the dimension value

b) You could replace your second dimension type by using an expression for each type value.

c) You could create all possible combinations of dimension values in your script (no need to assign reg values to any missing pair of dimension values, but the pairs do need to exist).

Maybe some other?

Please check attached file for these work-arounds.

Regards,

Stefan

[lfalmoguera]

Thanks a lot for the answer Swuehl.

Your solution works pretty well for short BD.

The problem I have is that I am loading a BD with millons of registers, and I have like over 20 different variables such as type. Therefore when I make the "joins" QV can handle the volume of the new table.

Any idea how to handle it?

I trully think QV should implement a solution for this issue so there is no need to "manually" fill the gaps of null values.

[swuehl]

I think what the last join between your large fact table and the dimension combinations table is not necessarily needed. You can get something equivalent with a concatenation:

TEST2:

LOAD Distinct Date as Date2 Resident Calendar;

Join LOAD Distinct type as type2 Resident Zero$;

LOAD Date as Date2, day as day2, type as type2, regs as regs2, AutoNumberHash128(Date,type) as Key2 Resident Zero$;

Concatenate LOAD * Resident TEST2 where not exists (Key2,AutoNumberHash128(Date2,type2));

drop table TEST2;

drop field Key2;

There is still one join to create all possible combinations for your dimension values, but this should be manageable, I hope.

[lfalmoguera]

Thanks a lot,

It works with a few dimensions (4-5) but as we increase the number (I have to reach over 20), the doc gets too heavy and I run out of free memory.

The original code is as follows:

FOR EACH vFile IN FileList ('$(dataPath)\archive\& '\2*_file*.txt')

TRAFFIC:
LOAD dateYYYYMMDD,
  MID(TEXT(dateYYYYMMDD),7,2) as day,
     MID(TEXT(dateYYYYMMDD),5,2) as month,
     MID(TEXT(dateYYYYMMDD),1,4) as year,
     text(MID(TEXT(dateYYYYMMDD),1,4)& MID(TEXT(dateYYYYMMDD),5,2)) as monthYear,
     DATE(MID(TEXT(dateYYYYMMDD),7,2) & '/' & MID(TEXT(dateYYYYMMDD),5,2) & '/' & MID(TEXT(dateYYYYMMDD),1,4),'YYYYMMDD') AS Date,
     code,
     descCode,

     sent,
     routeIn,
     routeOut,
     counrtyDesc,
     descSubroute,
     operatorDesc,
     durMinute,
     regs,
     price,
     'TOTAL' as total,
     if(code = 'XXXXX', '1', '0') as flagPriceSim
FROM
$(vFile)
(txt, codepage is 1252, embedded labels, delimiter is '\t', no quotes)
WHERE(text(sent) <> '0')
AND durMinute>0;

next vFile;

//CALENDAR_DATE_MIN_MAX:
LOAD
  min(Date) as MinDate,
  max(Date) as MaxDate

RESIDENT TRAFFIC;

LET Init_Date = peek('MinDate');
LET Final_Date = peek('MaxDate');

DROP TABLE DATE_MIN_MAX;

CALENDAR:
load Date($(Init_Date) - 1 + RecNo(), 'DD/MM/YYYY') as Date,
If(Num(Month(Date($(Init_Date) - 1 + RecNo(), 'DD/MM/YYYY'))) < 7,
Year(Date($(Init_Date) - 1 + RecNo(), 'DD/MM/YYYY')) - 1,
Year(Date($(Init_Date) - 1 + RecNo(), 'DD/MM/YYYY'))) as FiscalYear
autogenerate ($(Final_Date) - $(Init_Date) + 1);

[lfalmoguera]

Any other idea : (

I do not believe QV has this bug... or needs such a complicated solution.

Thanks a lot in advance.

[swuehl]

Store your large fact table into qvd, drop the table and load from qvd instead of a resident load in above snippet.

If you think this is a bug, call QV support.

But I assume they won't agree and consider this as work as expected.

But maybe they agree and / or come up with a much better workaorund / fix.

Regards,

Stefam