Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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,
Thanks, but it didnt work.. /:
same problem.. hmm. would it maby help to join tables to avoid the loop?
Final with TimeForbrukHisttemperatur
any suggestion ?
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
TimeforbrukHistTemperatur table is connected to TimeForbrukHistVaktGruppe - > Kalendar and Facts/kontrakt
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
OR
join both the tables
TimeforbrukHistvaktGruppe and Kontrakt
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?