Create Nulls If Related data doesn't exist.

[#1]

i have a table containing 3 fields. i.e . dates ,items, quantity.  i want that if i select a particular date for eg... 20/sept/2012. then ,, in table box i need that all the possibles values that culd have been for that date.. for eg.. if items field contained 3 items a,b,c.. and suppose in above mentioned date a item was cosumed.. then when i select that date i will get only a data in normal condition . but i want that when i select that date i get a, as well as b and c also but in place of b and c i will have null as quantity. and for a i will have actual quantity involved... please help me.. here is that excel file i have...

Thank you in advance.

[bumin]

you can use the calender object

[bumin]

something like this

put your date field into SaldoDatum

------------------------------

LET varMinDatum = num( MonthsStart(1,peek('SaldoDatumSort', 0, 'SaldenSort'),0)); // Erster des Monats nehmen

LET varMaxDatum = num(peek('SaldoDatumSort', -1, 'SaldenSort'));

//*************Temporäre Datumstabelle*************

Datumsfeld:

LOAD date($(varMinDatum)+IterNo()-1) AS Datumsfeld

AUTOGENERATE(1)

WHILE $(varMinDatum)+IterNo()-1<=$(varMaxDatum) ;

DROP Table SaldenSort;

//*************Kalendar Tabelle*************

Kalendar:

LOAD

        Datumsfeld AS SaldoDatum,

        Week(Datumsfeld) AS Woche,

        Year(Datumsfeld) AS Jahr,

        Month(Datumsfeld) AS Monat,

        Day(Datumsfeld) AS Tag,

        Weekday(Datumsfeld) AS Wochentag,

        applymap('Quartale_Mapping', month(Datumsfeld), null()) AS Quartal,

        date(monthstart(Datumsfeld),'MMM-YYYY') AS [Eindeutiger Monat],

        Week(Datumsfeld)&'-'&year(Datumsfeld) AS [Eindeutige Woche],

        Year2Date(Datumsfeld)*-1 AS YTDLaufendesJahr,

        Year2Date(Datumsfeld,-1)*-1 AS YTDLetztesJahr

RESIDENT Datumsfeld;

DROP TABLE Datumsfeld;

[#4]

Thank You for your answer. but i had tried this way before and it was not actually working..

[bumin]

Hi,

I didn't understand really what are you looking for. Someting like this?

see attached

[#6]

ya..thank you so much.but i want same answer but both dimensions in y axis..

[#7]

But the solution you have provided is in front end and the dimension items is taken on x-axis. but i need the same result if both the dimensions are in x-axis and from back-end.

[brijesh1991]

Calculate your expression from backend with group by required dimentions.

And then you can write if condition like if(IsNull(CalcuatedExpression),null(),CalcuatedExpression))

[#9]

But I am not geeting that result in this way also. i did group by. but it shows null in this case only if all the three items are not available for a particular date.if any one is there then it doesnt show nulls for remaining two.

[tresesco]

Have a look at the sample here suggested by John Witherspoon :