topic Re: How to associate these 2 tables? in Connectivity & Data Prep

How to associate these 2 tables?

Calogero — Fri, 17 Dec 2021 12:42:59 GMT

Hi dear community,

I have those 2 tables :

1) Absences_types

Type_Absence	Type_Absence_FR
P1	Accident - 1st month
P2	Accident - 2nd-12th month
P3	Accident - After 1 year
A1	Work Accident - 1st month
A2	Work Accident - After 1st month
Z1	Illness - 1st month
Z2	Illness - 2nd-12th month
Z3	Illness - After 1 year

2) Absences_FR (csv attached) (With Qty of Type_Absence and Frequency Type_Absnece)

I'm sure that there is a link between them but I don't know how to associate them in Qlik Sense (Data Manager).

Could you please help me ?

Thank you,

Regards.

Re: How to associate these 2 tables?

anat — Sat, 18 Dec 2021 08:55:15 GMT

test:

crosstable(desc,name,12)
LOAD [Firm ID],
[Department ID],
[Category ID],
[Person ID],
Year,
Quarter,
Month,
Date,
Period,
Qty_Days_Worked,
Qty_Working_Days,
Qty_Illness_Days,
Qty_Z0_Days,
Qty_Z1_Days,
Qty_Z2_Days,
Qty_Z3_Days,
Qty_P0_Days,
Qty_P1_Days,
Qty_P2_Days,
Qty_P3_Days,
Qty_A1_Days,
Qty_A2_Days,
Freq_Z0_Days,
Freq_Z1_Days,
Freq_Z2_Days,
Freq_Z3_Daqs,
Freq_P0_Days,
Freq_P1_Days,
Freq_P2_Days,
Freq_P3_Days,
Freq_A1_Days,
Freq_A2_Days
FROM
[..\Downloads\ABSENCES.csv]
(txt, utf8, embedded labels, delimiter is ';', msq);

noconcatenate
load * , subfield(desc,'_',2) as Type_Absence resident test;

drop table test;

exit script;

Re: How to associate these 2 tables?

Calogero — Sat, 18 Dec 2021 09:36:48 GMT

Hi dear Anat and thank you for your help.

I tried it and I replaced this line because I already have the file loaded in Qlik.

FROM
[..\Downloads\ABSENCES.csv]
(txt, utf8, embedded labels, delimiter is ';', msq);

I have the error hereunder.

Thank you,

Regards.

Re: How to associate these 2 tables?

anat — Sun, 19 Dec 2021 03:20:20 GMT

Check your source file path..

Re: How to associate these 2 tables?

Calogero — Sun, 19 Dec 2021 06:59:38 GMT

Hi, yes I did it and it works thank you.

If I don't use data manager but only the script, the result would be the same?

Thank you

Re: How to associate these 2 tables?

Calogero — Tue, 21 Dec 2021 10:44:04 GMT

Hi dear Anat,

Find attached the test that I made. (Qvf)

I don't know if it is the result reached.

Thank you 🙂