Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
KateQ
Contributor II
Contributor II

Ranking in script or another method

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?

IMG_20220419_185854_665.jpg

Labels (1)
9 Replies
Ksrinivasan
Specialist
Specialist

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
Champion III
Champion III

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

 

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
KateQ
Contributor II
Contributor II
Author

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

IMG_20220420_155201_715.jpg

vinieme12
Champion III
Champion III

please post some sample data,

 

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
KateQ
Contributor II
Contributor II
Author

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
Champion III
Champion III

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;

 

 

outputoutput

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
KateQ
Contributor II
Contributor II
Author

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

KateQ_7-1652375967509.jpeg

 

vinieme12
Champion III
Champion III

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)

 

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
KateQ
Contributor II
Contributor II
Author

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