Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Create Nulls If Related data doesn't exist.

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.

Tags (1)
10 Replies
bumin
Contributor II

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

you can use the calender object

bumin
Contributor II

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

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;

Not applicable

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

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

bumin
Contributor II

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

Hi,

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

see attached

Not applicable

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

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

Not applicable

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

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
Valued Contributor

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

Calculate your expression from backend with group by required dimentions.

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

Not applicable

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

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.

MVP
MVP

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

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

Community Browser