Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
xxcarlosxx
Contributor III

RANK() y OVER PARTITION()

Hello experts.
Can you help me with this?
I have this query made in Oracle and I want to replicate it in Qlik, 
but I don't know if there is any formula similar to OVER

Hola Expertos.

¿Podrán ayudarme con esto?

Tengo esta consulta hecha en Oracle y quiero replicarlo en Qlik, pero desconozco si exista alguna formula similar al OVER

Tabla Principal (Main Table):

LOAD * INLINE [
PERIODO_ID, TIPO_CAMBIO_CONTABLE
20220630, 3.75
20220531, 3.701
20220430, 3.834
20220331, 3.698
20220228, 3.756
20220131, 3.843
20211231, 3.987
20211130, 4.062
20211031, 3.989
20210930, 4.134
20210831, 4.084
20210731, 4.04
20210630, 3.858
] ;

xxcarlosxx_1-1654725596573.png

Consulta Oracle (Oracle Query):

SELECT tipo_cambio_contable
FROM
(
SELECT periodo_id, tipo_cambio_contable, rank() OVER(ORDER BY periodo_id DESC) AS rnk
FROM FCT_TCC_MENSUAL
WHERE periodo_id < 20220331
AND periodo_id > TO_NUMBER(TO_CHAR(TO_DATE(20220331, 'YYYYMMDD') - 90, 'YYYYMMDD'))
)
WHERE rnk = 1;

Resultado de la consulta (Result):

xxcarlosxx_2-1654725670893.png

Muchas gracias por su tiempo.

 

 

Labels (4)
2 Solutions

Accepted Solutions
tensini
Contributor II

[TMP_Table]:
LOAD * INLINE [
PERIODO_ID, TIPO_CAMBIO_CONTABLE
20220630, 3.75
20220531, 3.701
20220430, 3.834
20220331, 3.698
20220228, 3.756
20220131, 3.843
20211231, 3.987
20211130, 4.062
20211031, 3.989
20210930, 4.134
20210831, 4.084
20210731, 4.04
20210630, 3.858
]
Where PERIODO_ID < 20220331 and Date#(PERIODO_ID,'YYYYMMDD') > Date#(20220331,'YYYYMMDD')-90
;

Table:
LOAD
PERIODO_ID,
TIPO_CAMBIO_CONTABLE,
RowNo() as Rank
Resident[TMP_Table]
Order By TIPO_CAMBIO_CONTABLE asc;

DROP Table [TMP_Table];

View solution in original post

vinieme12
Champion III

You can still use your sql query in Qlik Sense to load the data

 

Lib Connect to "data connection to database";

 

TableName:

SQL   

<your sql query> ;

Disconnect;

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

View solution in original post

4 Replies
tensini
Contributor II

[TMP_Table]:
LOAD * INLINE [
PERIODO_ID, TIPO_CAMBIO_CONTABLE
20220630, 3.75
20220531, 3.701
20220430, 3.834
20220331, 3.698
20220228, 3.756
20220131, 3.843
20211231, 3.987
20211130, 4.062
20211031, 3.989
20210930, 4.134
20210831, 4.084
20210731, 4.04
20210630, 3.858
]
Where PERIODO_ID < 20220331 and Date#(PERIODO_ID,'YYYYMMDD') > Date#(20220331,'YYYYMMDD')-90
;

Table:
LOAD
PERIODO_ID,
TIPO_CAMBIO_CONTABLE,
RowNo() as Rank
Resident[TMP_Table]
Order By TIPO_CAMBIO_CONTABLE asc;

DROP Table [TMP_Table];

vinieme12
Champion III

You can still use your sql query in Qlik Sense to load the data

 

Lib Connect to "data connection to database";

 

TableName:

SQL   

<your sql query> ;

Disconnect;

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

Muchas gracias @tensini  y @vinieme12  por su tiempo y ayuda.

Thank you very much @tensini and @vinieme12 for your time and help.

 

xxcarlosxx
Contributor III
Author

Hello, one last question, how can I make the date, instead of being fixed, 
take the values ​​of the PERIODO_ID column?

Hola una ultima consulta, como puedo hacer para que la fecha en vez que sea fijo, tome los valores de la columna PERIODO_ID

Where PERIODO_ID < [COLUMNA PERIODO]  and Date#(PERIODO_ID,'YYYYMMDD') > Date#([COLUMNA PERIODO],'YYYYMMDD')-90

Gracias!!