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]

Were you able to do it? Let me know in case it didn't work.

[ritaaguiar]

@pradosh_thakur

I've never coded with Qlik Sense before, so I'm not sure what variables/parameters to write on the code.

[pradosh_thakur]

Well, The canonical calender code is already available right? Tell me where exactly you are stuck and i will help out.

[ritaaguiar]

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

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

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

Load
[Data1.Data],
[Data2.Data], [Data3.Data] as CanonicalDate,
'Data' as DateType
Resident OrderLines;

After Load I wrote the data file followed by the date field. 

What should be written on ApplyMap? Seems like I got it wrong. 

[pradosh_thakur]

Answer the following, Please make sure the names are correct and the case are also same

1: What are the date columns and which table is it in ? what column has the lowest granularity in the table

 for example

date1 -table1 -id1

date2 - table2 - id2

[ritaaguiar]

ok @pradosh_thakur

All date columns are named: Data (date in portuguese language) for all Data files, which are named Data1, Data2 and Data3.

so, all date columns are Data1.Data, Data2.Data and Data3.Data

What do you mean by lowest granularity?

[pradosh_thakur]

The column which can identify the rows uniquely? generally some kind of id etc.

Please give the name  of column and table name as i asked. It will be easier for me to help.

[ritaaguiar]

Table Name: Data1; Column Name: Data

Table Name: Data2; Column Name: Data

Table Name: Data3; Column Name: Data

[pradosh_thakur]

The dates are different dates ? i mean they signify different things or the same thing?
For example in the cannonical calender all the dates signify different type of dates ? If they are same you don't need to use cannonical calender . How does your data model loo like ?

[ritaaguiar]

 The dates signify dates. 02/11/2018 is 2nd November of 2018.

 I named the fields in portuguese.

Data (Date), Utilizador (Username), Programa (Program), Tempo (Time), Licença (Licence), Comando (Command), Hora (Hour).