topic Re: Create Nulls If Related data doesn't exist. in QlikView

Create Nulls If Related data doesn't exist.

Thu, 02 Jan 2014 12:01:57 GMT

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.

Re: Create Nulls If Related data doesn't exist.

bumin — Thu, 02 Jan 2014 12:18:44 GMT

you can use the calender object

Re: Create Nulls If Related data doesn't exist.

bumin — Thu, 02 Jan 2014 12:20:10 GMT

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;

Re: Create Nulls If Related data doesn't exist.

Thu, 02 Jan 2014 17:22:21 GMT

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

Re: Create Nulls If Related data doesn't exist.

bumin — Thu, 02 Jan 2014 19:47:39 GMT

Hi,

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

see attached

Re: Create Nulls If Related data doesn't exist.

Fri, 03 Jan 2014 04:16:01 GMT

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

Re: Create Nulls If Related data doesn't exist.

Fri, 03 Jan 2014 04:24:06 GMT

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.

Re: Create Nulls If Related data doesn't exist.

brijesh1991 — Fri, 03 Jan 2014 04:55:35 GMT

Calculate your expression from backend with group by required dimentions.

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

Re: Create Nulls If Related data doesn't exist.

Fri, 03 Jan 2014 06:47:08 GMT

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.

Re: Create Nulls If Related data doesn't exist.

tresB — Fri, 03 Jan 2014 07:05:07 GMT

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

Re: Create Nulls If Related data doesn't exist.

Fri, 03 Jan 2014 11:00:46 GMT

Sorry.. but it didnt work.