topic Re: Crosstable script calendar issue in QlikView

Crosstable script calendar issue

Anonymous — Wed, 02 Nov 2016 14:02:21 GMT

Hi,

i am trying to put in a new excel crosstable to my qv application.

How can i make my application understand that Måned (month) is a Month, and connect it to my calendar. ? so when I select a value it will look up the month regardless of year.

And how can i link the field MålepunktnavnTemp to the table TimeForbrukHistTemperatur without creating a syn table or loop?

the numbers in the table i have called GDTNORM.

script:

Graddagstallnorm:

CrossTable(Måned, GDTNORM, 2)

LOAD Stasjon,

MålepunktnavnTemperatur as MålepunktnavnTemp,

Jan,

Feb,

Mar,

Apr,

Mai,

Jun,

Jul,

Aug,

Sep,

Okt,

Nov,

Des

FROM.......

Final:

Load

Måned,

MålepunktnavnTemp,

GDTNORM

resident Graddagstallnorm;

drop table Graddagstallnorm;

TimeForbrukHistTemperatur:

LOAD

Date(Floor(FBDATO - 1/24)) as %DatoTEMP,

Date(Floor(FBDATO - 1/24)) &'-'& KLOKKEN as DatoKlokkenVGTEMP,

KLOKKEN as %KlokkenTEMP,

KWHVERDI as kWhTemp,

AVRSTATUS,

MLPKTNAMN as MålepunktnavnTemperatur

FROM..

Calendar:

/* ************************************************************************************

itelligence Standard Qlik Calender Torben Seebach/Martin Didriksen Special Thanks to Rob Wunderlich and John Witherspoon

Configure Calender parameters

Syntax:

Calendar(DateField[,Prefix[,Suffix[,FullCalendar]]])

Example:

Call Calendar('[Date (NK)]','MyCal','Pre.','','true'); // creates a the MyCal table and fields are prefixed with Pre. In the superfast variat

Call Calendar('[Date (NK)]'); //creates a table called calendar based of "Date (NK)" field

Mandatory:

@_DateField Contains the name of the field to connect the calendar to

Optional:

@_CalendarName Contains the name of the calendar we create

@_CalendarPrefix A Prefix for all fields

@_CalendarSuffix A Suffix for all fields

@_FullCalendar If this contains a value then it creates a calendar with all dates from Min(Date) to Max(Date), default is the slowest option

************************************************************************************ */

Sub Calendar (_DateField, _CalendarName, _CalendarPrefix, _CalendarSuffix, _FullCalendar)

Let _StartTime = Now();

Let _CalendarName = If(Len('$(_CalendarName)')=0,'Calendar','$(_CalendarName)');

Let _CalendarPrefix = If(Len('$(_CalendarPrefix)')=0,'','$(_CalendarPrefix)');

Let _CalendarSuffix = If(Len('$(_CalendarSuffix)')=0,'','$(_CalendarSuffix)');

Let _FullCalendar = If(Len('$(_FullCalendar)')=0,1,0);

Let _DateField = PurgeChar(_DateField,'"[]');

Let _DateFormat = 'DD.MM.YYYY';

"$(_CalendarName)":

LOAD

Distinct

Date([$(_DateField)], '$(_DateFormat)' ) as [$(_DateField)],

Date([$(_DateField)], '$(_DateFormat)' ) as [$(_CalendarPrefix)Dato$(_CalendarSuffix)],

Text(Date([$(_DateField)], '$(_DateFormat)' )) as [$(_CalendarPrefix)Datotekst$(_CalendarSuffix)],

Year([$(_DateField)]) as [$(_CalendarPrefix)År$(_CalendarSuffix)],

Year([$(_DateField)])&'-'&Month([$(_DateField)]) as [$(_CalendarPrefix)År-Måned$(_CalendarSuffix)],

week([$(_DateField)])&'-'&Year([$(_DateField)]) as [$(_CalendarPrefix)Uke-År$(_CalendarSuffix)],

week([$(_DateField)]) as [$(_CalendarPrefix)Uke$(_CalendarSuffix)],

Month([$(_DateField)]) as [$(_CalendarPrefix)Måned$(_CalendarSuffix)],

//Hour([$(_DateField)])&'-'&Minute([$(_DateField)]) as [$(_CalendarPrefix)Klokken$(_CalendarSuffix)],

Num(Month([$(_DateField)])) as [$(_CalendarPrefix)Månedsnummer$(_CalendarSuffix)],

Day([$(_DateField)]) as [$(_CalendarPrefix)Dag$(_CalendarSuffix)],

WeekDay([$(_DateField)]) as [$(_CalendarPrefix)Ukedag$(_CalendarSuffix)],

Year([$(_DateField)])&'-'&Text(Date([$(_DateField)],'MM')) as [$(_CalendarPrefix)År-måned$(_CalendarSuffix)],

'Q'&Ceil(Month([$(_DateField)])/3) as [$(_CalendarPrefix)Kvartal$(_CalendarSuffix)],

AutoNumber(MonthStart([$(_DateField)]),'_MonthSerial') as [$(_CalendarPrefix)Månedsløpenummer$(_CalendarSuffix)],

AutoNumber(QuarterStart([$(_DateField)]),'_QuarterSerial') as [$(_CalendarPrefix)Kvartalsløpenummer$(_CalendarSuffix)],

AutoNumber(weekyear([$(_DateField)]) &'|'&week([$(_DateField)]),'_WeekSerial') as [$(_CalendarPrefix)Ukeløpenummer$(_CalendarSuffix)] ;

If _FullCalendar=1 Then

Load Num(_DateStart+(Iterno()-1)) as [$(_DateField)]

While (_DateStart+(Iterno()-1)<=_DateStop);

LOAD

Floor(Min(Fieldvalue('$(_DateField)',RecNo()))) as _DateStart,

Floor(Max(Fieldvalue('$(_DateField)',RecNo()))) as _DateStop

AUTOGENERATE FieldValueCount('$(_DateField)');

Else

LOAD Num(Fieldvalue('$(_DateField)',RecNo())) as [$(_DateField)]

AUTOGENERATE FieldValueCount('$(_DateField)');

End If

/* clean variables */

Let _TotalTime = Round((Now()-_StartTime)*60*60*24,0.00000000001);

Let _StartTime = NULL;

Let _vDateStart = NULL;

Let _vDateStop = NULL;

Trace $(_CalendarName) created in: $(_TotalTime) seconds;

Let _TotalTime = NULL;

end sub

Re: Crosstable script calendar issue

sunny_talwar — Wed, 02 Nov 2016 14:06:42 GMT

May be this?

Graddagstallnorm:

CrossTable(Måned, GDTNORM, 2)

LOAD Stasjon,

MålepunktnavnTemperatur as MålepunktnavnTemp,

Jan,

Feb,

Mar,

Apr,

Mai,

Jun,

Jul,

Aug,

Sep,

Okt,

Nov,

Des

FROM.......

Final:

Load

Month(Date#(Måned, 'MMM')) as Måned,

MålepunktnavnTemp,

GDTNORM

resident Graddagstallnorm;

drop table Graddagstallnorm;

Re: Crosstable script calendar issue

avinashelite — Wed, 02 Nov 2016 14:10:13 GMT

Try like this

Final:

Load

Måned,

MålepunktnavnTemp,

GDTNORM

resident Graddagstallnorm;

drop table Graddagstallnorm;

in calender add some thing like this

Month([$(_DateField)]) as Måned,

Re: Crosstable script calendar issue

Anonymous — Wed, 02 Nov 2016 14:14:07 GMT

Thanks, but it didnt work.. /:

Re: Crosstable script calendar issue

Anonymous — Wed, 02 Nov 2016 14:23:37 GMT

same problem.. hmm. would it maby help to join tables to avoid the loop?

Final with TimeForbrukHisttemperatur

any suggestion ?

Re: Crosstable script calendar issue

avinashelite — Wed, 02 Nov 2016 14:30:05 GMT

I don't think the Måned field is creating the loop ...you need to deal with the other table fields

share the complete data model view so that we could suggest on the same

Re: Crosstable script calendar issue

Anonymous — Wed, 02 Nov 2016 14:34:28 GMT

Re: Crosstable script calendar issue

Anonymous — Wed, 02 Nov 2016 14:37:02 GMT

TimeforbrukHistTemperatur table is connected to TimeForbrukHistVaktGruppe - > Kalendar and Facts/kontrakt

Re: Crosstable script calendar issue

avinashelite — Wed, 02 Nov 2016 14:39:27 GMT

2 ways to resolve this

1.Create a composite key between

TimeforbrukHistvaktGruppe:

DateKey&'-'&Kontraktgroupkey&'-'&Malepunkkey as New_key

and rename the fields

Kontrakt:

DateKey&'-'&Kontraktgroupkey&'-'&Malepunkkey as New_key

join both the tables

TimeforbrukHistvaktGruppe and Kontrakt

Re: Crosstable script calendar issue

Anonymous — Wed, 02 Nov 2016 21:01:42 GMT

thanks, almost finished. just one problem:

In the script, Kontrakt table:

Kontrakt:

LOAD *,

KontraktsPeriode&'-'&%MålepunktKey as KontraktPeriodeMålepunkt,

%DateKey&'-'&%KontraktsgruppeKey&'-'&%MålepunktKey as New_key (added this one)

resident Kontrakt_t

where wildmatch(Status,'A','O','S') and not isnull([Forv./leiet.]) and [Forv./leiet.] <> 'FI'

and KontraktStartDatoFilter<=$(vToday)

and not(date(KontraktStartDato)=date(KontraktSluttDato));

Drop table Kontrakt_t;

how can we add: %DateKey&'-'&%KontraktsgruppeKey&'-'&%MålepunktKey as New_key

without loosing data in the timehistvaktgruppe table because of:

where wildmatch(Status,'A','O','S') and not isnull([Forv./leiet.]) and [Forv./leiet.] <> 'FI'

and KontraktStartDatoFilter<=$(vToday)

and not(date(KontraktStartDato)=date(KontraktSluttDato));

any suggestions?

Re: Crosstable script calendar issue

MarcoWedel — Wed, 02 Nov 2016 23:25:52 GMT

Hi,

maybe instead of creating a separate final tablöe you should just join the Måned field:

Graddagstallnorm:

CrossTable(TempMåned, GDTNORM, 2)

LOAD Stasjon,

MålepunktnavnTemperatur as MålepunktnavnTemp,

Jan,

Feb,

Mar,

Apr,

Mai,

Jun,

Jul,

Aug,

Sep,

Okt,

Nov,

Des

FROM.......

Left Join (Graddagstallnorm)

LOAD Distinct

TempMåned,

Month(Date#(TempMåned,'MMM')) as Måned

Resident Graddagstallnorm;

Drop Field TempMåned;

hope this helps

regards

Marco

Re: Crosstable script calendar issue

Anonymous — Thu, 03 Nov 2016 07:56:52 GMT

not working...

when i reload, i dont get error. But the tables wont update... and all my tables in transform disapear.

Graddagstallnorm:

CrossTable(TempMåned, GDTNORM, 2)

LOAD Stasjon,

MålepunktnavnTemperatur,

Jan,

Feb,

Mar,

Apr,

Mai,

Jun,

Jul,

Aug,

Sep,

Okt,

Nov,

Des

FROM

(ooxml, embedded labels, table is Ark1);

	Left Join (Graddagstallnorm)
	LOAD Distinct
	TempMåned,
	Month(Date#(TempMåned,'MMM')) as Måned
	Resident Graddagstallnorm;


	Drop Field TempMåned;

Re: Crosstable script calendar issue

Anonymous — Thu, 03 Nov 2016 08:00:09 GMT

In the script, Kontrakt table:

Kontrakt:

LOAD *,

KontraktsPeriode&'-'&%MålepunktKey as KontraktPeriodeMålepunkt,

%DateKey&'-'&%KontraktsgruppeKey&'-'&%MålepunktKey as New_key // (added this one) //

resident Kontrakt_t

where wildmatch(Status,'A','O','S') and not isnull([Forv./leiet.]) and [Forv./leiet.] <> 'FI'

and KontraktStartDatoFilter<=$(vToday)

and not(date(KontraktStartDato)=date(KontraktSluttDato));

Drop table Kontrakt_t;

how can we add: %DateKey&'-'&%KontraktsgruppeKey&'-'&%MålepunktKey as New_key

without loosing data in the timehistvaktgruppe table because of the filters:

where wildmatch(Status,'A','O','S') and not isnull([Forv./leiet.]) and [Forv./leiet.] <> 'FI'

and KontraktStartDatoFilter<=$(vToday)

and not(date(KontraktStartDato)=date(KontraktSluttDato));

any suggestions?