Solved: How to associate the date field from 3 data files? - Qlik Community

ritaaguiar · ‎2019-02-08

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 · ‎2019-02-14

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

Learning never stops.

View solution in original post

pradosh_thakur · ‎2019-02-15

Check out the below link to see how to load data using data load editor.
https://help.qlik.com/en-US/sense/November2018/Subsystems/Hub/Content/Sense_Hub/LoadData/use-data-lo...

A simple google search can give you may results. I can suggest you videos of michale Tarallo of Qlik. They will help you understand the data more.

Learning never stops.

View solution in original post

pradosh_thakur · ‎2019-02-08

Have a look
https://community.qlik.com/t5/Qlik-Design-Blog/Canonical-Date/ba-p/1463578

Learning never stops.

pradosh_thakur · ‎2019-02-08

https://community.qlik.com/t5/QlikView-Documents/Linking-to-two-or-more-dates/ta-p/1495322

Learning never stops.

ritaaguiar · ‎2019-02-08

Thank you @pradosh_thakur

To write those lines of code and create a canonical calendar, do I have to unlock the auto-generated script on the data manager? Because if I do so, then I can no longer edit data on the Data Manager.

I'm doing it on a copy file, just to make sure I don't mess it up...

I tried this: But I don't really know what to put on some of the variables/parameters. I tried to load it but it doesn't work.

Load
[Data1.Data],
Applymap('Data1.Data',Data,Null()) as CanonicalDate,
'Data1' as DateType
Resident OrderLines;

Load
[DadosHorasTrabalho.Data],
Applymap('DadosHorasTrabalho.Data2Data',Data,Null()) as CanonicalDate,
'Data2' as DateType
Resident OrderLines;
Load
[DadosInOut.Data],
Applymap('DadosInOut.Data2Data',Data,Null()) as CanonicalDate,
'Data3' as DateType
Resident OrderLines;

Load
[Data1.Data],
[DadosHorasTrabalho.Data2Data], [DadosInOut.Data] as CanonicalDate,
'Data' as DateType
Resident OrderLines;

@pradosh_thakur
I cannot open the .qvw file on the second link. It says that 'the licence that is embedded in this document is not valid'.

pradosh_thakur · ‎2019-02-08

Try adding the canonical code in another tab . don't disrupt/edit the autogenerate section and used your canonical fields. I hope this fits your requirement.

Learning never stops.

pradosh_thakur · ‎2019-02-08

I have a personal edition so can't open . 😞

Learning never stops.

ritaaguiar · ‎2019-02-08

I ended up doing it on the Auto Generated Section, but on a copy file and so the non edited file is safe.

How can I get the variables/parameters right?

I don't understand some of the variables/paramers on the first link https://community.qlik.com/t5/Qlik-Design-Blog/Canonical-Date/ba-p/1463578. I tried doing my best to interpret it, but it's not working 😞

pradosh_thakur · ‎2019-02-08

It is always advisable to have a copy and not do on your main app as we don't know what the data is exactly and can cause irreversible issue unknowingly. So always try on an extra/dummy app.

Learning never stops.

ritaaguiar · ‎2019-02-08

@pradosh_thakur Yes, as I said, I ended up doing it on the Auto Generated Section of a file copy and so the file is safe.

pradosh_thakur · ‎2019-02-08

We as a developer eventually get a issue where we have to go beyond master calender so congratulations, you are on good track.
About the error, it is because you don't have mapping tables. In the comments Michel has described how to create mapping table from this. Please use the lowest granularity as your id there. Read the comments you will understand.

Learning never stops.