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.
zeroDate_qv11_SW.qvw 177.0 K
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.
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:
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.
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')
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,
'TOTAL' as total,
if(code = 'XXXXX', '1', '0') as flagPriceSim
(txt, codepage is 1252, embedded labels, delimiter is '\t', no quotes)
WHERE(text(sent) <> '0')
min(Date) as MinDate,
max(Date) as MaxDate
LET Init_Date = peek('MinDate');
LET Final_Date = peek('MaxDate');
DROP TABLE DATE_MIN_MAX;
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);
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.