Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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...