How to associate the date field from 3 data files?

[ritaaguiar]

Hi there,

I have a Qlik Sense with three .txt data files. Each of the .txt data files has three fields in commom: Utilizador (Username), Data (Date), and Programa (Program).

I woould like to have a field block on my sheet which filters all graphics with data from any of these three files.

How can I get a date field which merges all three and thus affects all graphics from any of the 3 data files?

 

 You may notice that the Username field (Utilizador) and the Program field (Programa) are both successfully filtering all graphics. I would like that to happen to the date field as well.

I have tried to associate the Date fields but everytime I try that it dissassociates the other Username and Program fields...

[pradosh_thakur]

Are you sure data2 and data3 are named properly ?

[ritaaguiar]

@pradosh_thakur  yes. Why?

[pradosh_thakur]

I am working on the older sample you have posted. Let me update you in a while. Looks like i would need a understanding of data.

[ritaaguiar]

I have changed the file names to english for better understanding.

The first file Data1 is the data obtained from monotoring the use that the username (column 1) gives to the program (column 2) at his work. Each command that the user performs in the software Autodesk AutoCAD is registered: date (column 3), hour (column 4) and the command used (column 5).

The files Data2 and Data3 are the result of processing the first Data1 file, through a plugin.

Data2 shows the time spent by the user (column 1) using the program (column 2) on that day (column 3), where 1 stands for 100% of time spent in the program out of 7,5 hours a day of work (column 4).

Data3 register only when the user (column 1) launched or closed the program (column 2). When (date and hour columns 3 and 4) the user launches the program he summons a licence  (value +1 column 5) and when the user closes the program he frees the licence for network use (value -1 column 5).

[pradosh_thakur]

I did a simple join between the three table and got the data set. Just have a look if this is something you want

data3:
load * inline [
Utilizador,Programa,Data,Hora,Licença
rita.aguiar,Autodesk AutoCAD 2019,04/02/2019,09:00:00,1
rita.aguiar,Autodesk AutoCAD 2019,04/02/2019,10:00:00,-1
rita.aguiar,Autodesk AutoCAD 2019,04/02/2019,10:00:00,1
rita.aguiar,Autodesk AutoCAD 2019,04/02/2019,11:00:00,-1
rita.aguiar,Autodesk Revit 2019,04/02/2019,11:00:00,1
rita.aguiar,Autodesk Revit 2019,04/02/2019,12:00:00,-1
rita.aguiar,Autodesk AutoCAD Civil 3D 2019,04/02/2019,12:00:00,1
rita.aguiar,Autodesk AutoCAD Civil 3D 2019,04/02/2019,13:00:00,-1
];
 
 join
data2:
load * inline [
Utilizador,Programa,Data,Tempo
rita.aguiar,Autodesk AutoCAD 2019,04/02/2019,0.266666666666667
rita.aguiar,Autodesk Revit 2019,04/02/2019,0.133333333333333
rita.aguiar,Autodesk AutoCAD Civil 3D 2019,04/02/2019,0.133333333333333
];
join
data1:
load * inline [
Utilizador,Data,Hora,Programa,Comando
rita.aguiar,04/02/2019,09:00:00,Autodesk AutoCAD 2019,iniciar
rita.aguiar,04/02/2019,10:00:00,Autodesk AutoCAD 2019,encerrar
rita.aguiar,04/02/2019,10:00:00,Autodesk AutoCAD 2019,iniciar
rita.aguiar,04/02/2019,11:00:00,Autodesk AutoCAD 2019,encerrar
rita.aguiar,04/02/2019,11:00:00,Autodesk Revit 2019,iniciar
rita.aguiar,04/02/2019,12:00:00,Autodesk Revit 2019,encerrar
rita.aguiar,04/02/2019,12:00:00,Autodesk AutoCAD Civil 3D 2019,iniciar
rita.aguiar,04/02/2019,13:00:00,Autodesk AutoCAD Civil 3D 2019,encerrar
];

[pradosh_thakur]

In the above example you can change the load * inline [ ..... ]; to your excel file load.

[ritaaguiar]

@pradosh_thakur 

I'm not sure, the date and hour fields don't look right

[pradosh_thakur · Accepted solution]

data1:
load *,Utilizador& Programa&  Data as join_key inline [
Utilizador,Data,Hora,Programa,Comando
rita.aguiar,04/02/2019,09:00:00,Autodesk AutoCAD 2019,iniciar
rita.aguiar,04/02/2019,10:00:00,Autodesk AutoCAD 2019,encerrar
rita.aguiar,04/02/2019,10:00:00,Autodesk AutoCAD 2019,iniciar
rita.aguiar,04/02/2019,11:00:00,Autodesk AutoCAD 2019,encerrar
rita.aguiar,04/02/2019,11:00:00,Autodesk Revit 2019,iniciar
rita.aguiar,04/02/2019,12:00:00,Autodesk Revit 2019,encerrar
rita.aguiar,04/02/2019,12:00:00,Autodesk AutoCAD Civil 3D 2019,iniciar
rita.aguiar,04/02/2019,13:00:00,Autodesk AutoCAD Civil 3D 2019,encerrar
];


data2:
load Utilizador& Programa&  Data as join_key,Tempo  inline [
Utilizador,Programa,Data,Tempo
rita.aguiar,Autodesk AutoCAD 2019,04/02/2019,0.266666666666667
rita.aguiar,Autodesk Revit 2019,04/02/2019,0.133333333333333
rita.aguiar,Autodesk AutoCAD Civil 3D 2019,04/02/2019,0.133333333333333
];


data3:
load Utilizador& Programa&  Data as join_key , Hora as closing_hour ,Licença;
load * inline [
Utilizador,Programa,Data,Hora,Licença
rita.aguiar,Autodesk AutoCAD 2019,04/02/2019,09:00:00,1
rita.aguiar,Autodesk AutoCAD 2019,04/02/2019,10:00:00,-1
rita.aguiar,Autodesk AutoCAD 2019,04/02/2019,10:00:00,1
rita.aguiar,Autodesk AutoCAD 2019,04/02/2019,11:00:00,-1
rita.aguiar,Autodesk Revit 2019,04/02/2019,11:00:00,1
rita.aguiar,Autodesk Revit 2019,04/02/2019,12:00:00,-1
rita.aguiar,Autodesk AutoCAD Civil 3D 2019,04/02/2019,12:00:00,1
rita.aguiar,Autodesk AutoCAD Civil 3D 2019,04/02/2019,13:00:00,-1
];

How about the above . I didn't see the explanation of yours before writing the code

I will tell you what i did. As you have User , date and programme they have to be the join key between the three tables. As the second table is the output of first table we can join it so that we can get the tempo, which will show the time spent by employee ( out of 1).

In the third table the key to join again is the three fields(User , date and programme). The hours when the user summons the license (hour) which is different the hour in the first table hence not joined . and the value is also not joined.

Hope i made sense .Let me know if you need anything else.

Thanks

Pradosh

[ritaaguiar]

@pradosh_thakur 

I added an entrance on 05/02/2019 because I forgot that I needed two dates to  test if the date is being filtered correctly.

It seems like it's working fine. However, having in mind that this was a sample data, and that I will have at least hundreds of thousands of lines, if not millions of lines of entrances on Data1, Data2 and Data3, it doesn't seem very feasible to introduce this data manually in the script. Any ideias on how to do it? I inserted all data I have so far, manually on the script, and tried loading the data, but Qlik Sense is not responding.

Also, I was hoping that the fields Hora, Tempo and Licença would show all entrances listed, although it's not really a big deal. Probably I will not need these fileds to filter any data.

Thank you very much for your invaluable help so far.no filter

filter for 04/02/2019filter for 05/02/2019

[pradosh_thakur]

@ritaaguiar  I added it manually because  I just wanted to test it. You don't have to add them manually by typing . I can work out a qvf for you.