Création intervalles d'heures

[LouisD]

Bonjour à tous

Je suis nouveau sur Qlik et j'ai un petit soucis que je n'arrive pas à résoudre, peut être saurez vous éclairer ma lanterne :

J'ai des résultats de tests qui sont rangés dans un tableau en fonction de l'heure :

11:48	bien
12:06	assez bien
12:23	bien
12:46	bien

J'ai plus d'une centaine de tests sur la journée et j'aimerai grouper mes heures en intervalles par exemple de 00:00 à 06:00 et que Qlik me comptabilise le nombre de tests dans cet intervalle

J'ai bien essayé avec des COUNT et CONCAT mais je bloque....

Merci pour votre aide précieuse 🙂

[mato32188 · Accepted solution]

Hi Louis,

I do not speak French, but Google Translator is actually very good these days.

Script:

tmp:
load * Inline [
Time,Rating
11:48,bien
12:06,assez bien
12:23,bien
12:46,bien
];

intervals:
load * Inline [
IntervalStart, IntervalEnd
00:01, 06:00
06:01, 12:00
12:01, 18:00
18:00, 00:00
];

left join(tmp)
IntervalMatch(Time)
load IntervalStart, IntervalEnd Resident intervals;

drop table intervals;

Final:
Load
IntervalStart&' - '& IntervalEnd as Interval,
Time,
Rating
Resident tmp;
drop table tmp;

Visualization:

Stacked bar chart

Dims: Interval, Rating

Measure: Count (Rating)

For more information please find https://help.qlik.com/en-US/qlikview/April2020/Subsystems/Client/Content/QV_QlikView/Scripting/Scrip...

BR

m

[LouisD]

Hello Mato

Thank you very much for te reply and for the help, i wouldn't have figured this out by myself.

I will try this out today with my data and let you know if everthing works fine.

Thanks a lot, and thanks google trad 🙂

Have a nice day

Louis

[LouisD]

Hello again 🙂

I modified a little the script in the begining and added more intervals :

TEST2:
LOAD *inline [Ark1.Time, Ark1.TestResult];
LOAD Ark1.Time, Ark1.TestResult
Resident [MYTABLE];

intervals:
load * Inline [
IntervalStart, IntervalEnd
00:01, 06:00
06:01, 12:00
12:01, 18:00
18:00, 00:00
];

left join(TEST2)
IntervalMatch(Ark1.Time)
load IntervalStart, IntervalEnd Resident intervals;

drop table intervals;

Final:
Load
IntervalStart&'-'& IntervalEnd as Interval,
Ark1.Time,
Ark1.TestResult
Resident TEST2;
drop table TEST2;

The visualization works just fine too, just a problem with the first column '-', i do not know how to remove that properly. The last interval doesnt show up too in the vizualisation chart.

Thanks a LOT for your help

Have  a very nice day

Louis

[mato32188]

Hi Louis,

it is caused by my mistake. As you can see, last interval starts at 18:01 and ends 00:00. I forgot that we are working with 24h mode, not 12h mode. Quick fix: I would suggest you to change code below:

intervals:
load * Inline [
IntervalStart, IntervalEnd
00:00, 05:59
06:00, 11:59
12:00, 17:59
18:00, 23:59;

BR

m

];

[LouisD]

Hello Mato

It is working just fine 🙂

Thanks a lot have a very nice day

Louis