Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hey! We have 2 tables that need to be related 1 to 1. One record of the left table must match one record of the right table.
Where date_a>=date_c and date_a<=date_c+29. a1 corresponds to c1 with the minimum date_c from the interval by id=1. There is an idea to create in tables keys with a rank on dates. How can this be done in a script or can it be done differently?
Hi,
1. Create one new field in table2, that field will respect to id_a like a1 should c1, now you have created primary key in new field with respect to table1.
2. Now do left join, from table2 (new fielde) as %Ref1 and from table1 (id_c) as %Ref1.
3. Now create field like
If(id_a<=id_c, "match","NA") as Status,
In resident table from above table.
4. Use whereclass status ="match" data only considered in app data
5. You can create straight table as you expected result.
I must say this dataset is very ambiguous
Try with autonumber()
temp_Tablec:
Load id,id_c,date_c
From Xyz;
TableC:
Load *, id&'-'autonumber(id&date_c) as auto_id
Resident temp_Tablea
Order by id,id_c,date_c;
Drop table temp_Tablec;
temp_TableA:
Load id,id_a,date_a
From PQr;
TableA:
Load *, id&'-'autonumber(id&date_a) as auto_id
Resident temp_TableA
Order by id,id_a,date_a;
Drop table temp_TableA;
Your tables should now be associated by auto_id
Autonumber() continues numbering in the second table. Before that, I tried to use Autonamber(RowNo()), but RowNo() can only be used in one table, the result will also be incorrect in the second
please post some sample data,
claim:
LOAD * Inline[
id,id_c,date_c
1,c1,01.02.2021
1,c2,09.02.2021
1,c3,19.03.2021
1,c4,24.03.2021
2,c21,01.02.2021
2,c22,09.02.2021
2,c23,19.03.2021
2,c24,24.03.2021];
ank:
LOAD * Inline[
id,id_a,date_a
1,a1,10.02.2021
1,a2,11.02.2021
1,a3,19.03.2021
1,a4,24.05.2021
2,a21,08.02.2021
2,a22,12.02.2021
2,a23,18.03.2021
2,a24,23.05.2021];
here you go
claim:
LOAD id,id_c,date#(date_c,'DD.MM.YYYY') as date_c Inline [
id,id_c,date_c
1,c1,01.02.2021
1,c2,09.02.2021
1,c3,19.03.2021
1,c4,24.03.2021
2,c21,01.02.2021
2,c22,09.02.2021
2,c23,19.03.2021
2,c24,24.03.2021];
NoConcatenate
claim_:
Load
id as id_claim
,id_c
,date_c
,id&'-'&autonumber(id&date_c,'claim') as auto_id
Resident claim
Order by id,id_c,date_c asc;
Drop table claim;
ank:
LOAD id,id_a,date#(date_a,'DD.MM.YYYY') as date_a Inline [
id,id_a,date_a
1,a1,10.02.2021
1,a2,11.02.2021
1,a3,19.03.2021
1,a4,24.05.2021
2,a21,08.02.2021
2,a22,12.02.2021
2,a23,18.03.2021
2,a24,23.05.2021];
NoConcatenate
ank_:
Load
id as id_ank
,id_a
,date_a
,id&'-'&autonumber(id&date_a,'ank_') as auto_id
Resident ank
Order by id,id_a,date_a asc;
Drop table ank;
The condition date_a>=date_c and date_a<=date_c+29 must be taken into account
that would be incorrect when joining, there are multiple dates that would satisfy the criteria
claim:
date_c 01/02/2022 |
ank:
date_a |
10/02/2022 |
11/02/2022 |
10/02/2022 > 01/02/2022 and 10/02/2022 <= 02/03/2022 (01/02/2022 + 29)
11/02/2022 > 01/02/2022 and 11/02/2022 <= 02/03/2022 (01/02/2022 + 29)
The condition also states that you need to take the minimum date from the interval