topic Re: Make visitnumbers from inspectiondates in QlikView

Make visitnumbers from inspectiondates

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Good afternoon,

I am not very good in qlikview but I manage most of time, but some times it's a headbreaker and cannot find out how to solve or if there is a solution anyway. I am from the Netherlands and my English is also not very well, but I hoop understandable

I will try to explain my question

For each customer there are different inspection dates over the year. For each inspection there is a result in points.

I made a table with the results. First name of the company then later inspectiondate and the calculated score. Depending on the score it will color red, orange or green.

Most inportant is that we can see very fast the color result of each company. Because more times behind each other not a green color needs immidiatly action. So in this cas I want to make a crosstable!

Problem are the inspectiondates. They are for each visit diffirent. The result is showing in the next image:

It is showing for all the companys all the inspections dates available in the data. Now it also gives more green colors because the inspection date which is not there for a company is giving the value 0. (color green) It is not the calculated value.

I am searching for a load script to put a new value on the visitdate of each seperate company. (flag) Like for the first inspectiondate a label Visit 1, for the second inspectiondate a label Visit 2.. and so on...

For the cross table I can use the labels because the dates are not important, but the colors behind each other.

Example in Excel:

The data, by loading script the first inspection date for company A gets a label Visit 1, the second inspection date Visit 2, and so on

The cross table will look this way. You can immidiatly see the color results for each company.

Is this practicle possible?

Kindly regards,

Jan

Re: Make visitnumbers from inspectiondates

jaumecf23 — Mon, 19 Feb 2018 11:52:01 GMT

Hi,

I have attached a QVW that can give you a solution of your problem. Please check and reply if you have any concerns.

Re: Make visitnumbers from inspectiondates

Anonymous — Mon, 19 Feb 2018 19:15:43 GMT

Hi Jaume,

Exactly the way I wanted it... You don't know how happy I am with this. it's really great!

Now I only need an orderby clausule.

Each inspection has about 65 inspectionpoints. So in the table for each inspection point record there is the same inspectiondate.

And the results voor each inpspectionpoint in now a visitnumber... So there must be a kind of groupby in the script? Is that possible?

You really are great. This is so cool! Thanks Jaume!

Kindly regards, Jan

Re: Make visitnumbers from inspectiondates

Anonymous — Tue, 20 Feb 2018 06:06:41 GMT

Hi Jaume,

This morning I realise that I didn't put enough information in my last answer.

The data I use is coming from a SQL server.

As you can see is each record the same because each inspection has about 65 inspectionpoints.

(Uitvoerdat = inspectiondate)

When I use a Group By function it's shows the records I need.

So in the script Ik thought to load the data also by Group By:

Temp:
LOAD
DebtorNr,
Uitvoerdat,
//Date#(Uitvoerdat,'DD-MM-YYYY') as Date2;
Date#(Uitvoerdat,'DD-MM-YYYY') as Date2 GROUP BY DebtorNr, UitvoerDat;

//Sql Select DebtorNr, Uitvoerdat FROM "NBC_e-Connect". dbo.[NBC_QV_HygieneInspectie_B&B];
Sql SELECT DebtorNr, UitvoerDat FROM "NBC_e-Connect".dbo.[NBC_QV_HygieneInspectie_B&B] GROUP BY DebtorNr, UitvoerDat;

But this is nog accept because I get the error:

Where do I hav tuse this function?

Kindly Regards,

Jan

Re: Make visitnumbers from inspectiondates

jaumecf23 — Tue, 20 Feb 2018 08:25:54 GMT

Hi Jan,

When you use the Group By expression, the fields that are not included in the group by clause, for that fields is necessary to use a aggregation formula. Try this :

Temp:
LOAD
DebtorNr,
Uitvoerdat,
//Date#(Uitvoerdat,'DD-MM-YYYY') as Date2;
Max(Date#(Uitvoerdat,'DD-MM-YYYY')) as Date2

GROUP BY DebtorNr, UitvoerDat;

Re: Make visitnumbers from inspectiondates

Anonymous — Wed, 21 Feb 2018 10:57:39 GMT

Hi Jaume,

The GROUP BY was indeed the solution. Next problem was that the sort of the inspection dates where not equal whith the visitdate numbers. So I added finaly an ORDER BY

The perfect working script:

Temp:

LOAD

DebtorNr,

UitvoerDat,

Max(Date#(UitvoerDat,'DD-MM-YYYY')) as Date2

GROUP BY DebtorNr, UitvoerDat ;

Sql Select DebtorNr, UitvoerDat FROM "NBC_e-Connect". dbo.[NBC_QV_HygieneInspectie_B&B] ORDER BY DebtorNr, UitvoerDat;

NoConcatenate

Final:

Load *,

'Bez ' & VistNrTemp as VisitNr;

LOAD*,

if(previous(DebtorNr)=DebtorNr and not(IsNull(DebtorNr)),Peek(VistNrTemp)+1,1) as VistNrTemp

Resident Temp

Order by DebtorNr, Date2 asc;

Drop Table Temp;

The normal table, each company vertical...

and the new cross table:

Perfect Jaume!

Thanks a lot for your help...

Re: Make visitnumbers from inspectiondates

jaumecf23 — Wed, 21 Feb 2018 11:02:34 GMT

You're welcome!