topic Re: Re: How to add 10 different values for same date? in QlikView

How to add 10 different values for same date?

b04nhu13 — Thu, 24 Jul 2014 12:13:39 GMT

I want to add 10 different values to each day that only have the value ’00:00:00’ in another field (KLOCKANST)

The values is the same as you see in field "BOLAG" (Bolag02,Bolag04,Bolag,10 ...)

I add the dates that were missing and '00:00:00' in two fields highlighted in yellow. There is a lot of them.

SlaMigg:
LOAD
BOLAG,
DATUMST,
KLOCKANST,
DATUMOP,
KLOCKANOP,
Timmar,
Orsak;
SQL SELECT *
FROM "SLA_MIGG".dbo.Rapportunderlag
WHERE DATUMST >= '2013-03-31' ;

MinMax:
Load
Min(DATUMST) as MinDate,
Max(DATUMST) as MaxDate
resident SlaMigg;
LET vMinDate = Num(Peek('MinDate', 0, 'MinMax'));
LET vMaxDate = Num(Peek('MaxDate', 0, 'MinMax'));
Drop Table MinMax;

Join (SlaMigg)
Load Date(recno()+$(vMinDate)) as DATUMST Autogenerate vMaxDate - vMinDate;

Migg:
NoConcatenate Load DATUMST,
If( IsNull( KLOCKANST ),'00:00:00', KLOCKANST ) as KLOCKANST,
If( IsNull( KLOCKANOP ), '00:00:00', KLOCKANOP ) as KLOCKANOP,
BOLAG,
DATUMOP,
Timmar,
Orsak
Resident SlaMigg
Order By DATUMST ;
Drop Table SlaMigg;

the

Re: How to add 10 different values for same date?

sujeetsingh — Wed, 30 Jul 2014 08:14:07 GMT

It really seems correct .

Can you explain in more words.

Re: How to add 10 different values for same date?

jonathandienst — Wed, 30 Jul 2014 08:19:55 GMT

There was no data in Slamigg for those two dates, so all fields other than DATUMST where null after the Join. Your If(IsNull()) logic puts in default values for the KLOCK* fields, but you have not supplied defaults for BOLAG and the other fields when they are null.

HTH

Jonathan

Re: Re: How to add 10 different values for same date?

b04nhu13 — Wed, 30 Jul 2014 08:26:45 GMT

OK, but it isn´t

As you can see I add dates that were missing highlighted in yellow

But I have nothing in the "BOLAG" field. As you can see I need ten differet values for the same date

Re: Re: How to add 10 different values for same date?

b04nhu13 — Wed, 30 Jul 2014 08:32:26 GMT

I can add one BOLAG value

IF (IsNull (BOLAG), 'Dummy', BOLAG) as BOLAG

But I want all BOLAG values fore each day; Bolag02, Bolag04 ....

Re: Re: How to add 10 different values for same date?

b04nhu13 — Thu, 31 Jul 2014 10:48:05 GMT

I never solved the problem in qlikview. I change the data in SQL instead