Ranking in script or another method

[KateQ]

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?

 

[Ksrinivasan]

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.

[vinieme12]

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

[KateQ]

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

 

[vinieme12]

please post some sample data,

[KateQ]

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];

[vinieme12]

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;

output

[KateQ]

The condition date_a>=date_c and date_a<=date_c+29 must be taken into account

[vinieme12]

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)

[KateQ]

The condition also states that you need to take the minimum date from the interval