Hello everyone, I would like a script suggestion. In this table queue I need to pull only the records with the highest "Date Entry" pulling the NFe records where there is only the highest Date Entry:
I did the OUTER JOIN because there can be null dates.
To get the records with the maximum 'Data Entrada' for each 'NFe', you need to group the data by 'NFe' and take the max 'Data Entrada'. You can do this using Qlikview's GROUP BY and MAX functions. You might also want to use the WHERE EXISTS function to ensure that the 'NFe' values exist in the 'TABELA_NFE' table.
Here is a suggested script:
[TABELA_NFE]: LOAD NFe AS "NFe", Cotacao AS "Numero da SIC", Ordem_de_compra AS "Ordem de Compra" FROM [$(vGlobalDirQVDExtracao)/MOVMAT3.qvd](qvd);
Temp_ENTRADA_NFE1: LOAD MOVNF AS "NFe", DATE(MOVNFDATEN) AS "Data Entrada" FROM [$(vGlobalDirQVDExtracao)/MOVMAT2.qvd](qvd) WHERE EXISTS([NFe], MOVNF);
Max_ENTRADA_NFE1: LOAD "NFe", Max("Data Entrada") as "Max Data Entrada" RESIDENT Temp_ENTRADA_NFE1 GROUP BY "NFe";
DROP TABLE Temp_ENTRADA_NFE1;
LEFT JOIN (TABELA_NFE) LOAD "NFe", "Max Data Entrada" RESIDENT Max_ENTRADA_NFE1;
DROP TABLE Max_ENTRADA_NFE1;
Please note: This script will replace your original 'Data Entrada' with the maximum 'Data Entrada' for each 'NFe'. If you want to keep the original dates, you can give the max date a different name and keep the original table.
https://www.linkedin.com/in/cristianjorge/ Do not forget to mark as "Accepted Solution" the comment that resolves the doubt.
[TABELA_NFE]: LOAD NFe AS "NFe", Cotacao AS "Numero da SIC", Ordem_de_compra AS "Ordem de Compra" FROM [$(vGlobalDirQVDExtracao)/MOVMAT3.qvd](qvd);
[ENTRADA_NFE1]: LOAD MOVNF AS "NFe", DATE(MOVNFDATEN) AS "Data Entrada" FROM [$(vGlobalDirQVDExtracao)/MOVMAT2.qvd](qvd) Where not IsNull("Data Entrada");
Right Join ([TABELA_NFE]) ENTRADA_NFE1_MaxEntryDate : LOAD "NFe", Max("Data Entrada") as "Data Entrada"
