How to display only last updated data for columns which cause duplicate

[ManiSK]

HI,

Could someone please help me on below query.

Actually i have some 100 + files with (filename_datestamp.csv), here i'm concatenating all the files and generating report.

so here there are some 5 columns which causes duplicate records, so for those 5 columns alone i need to display the data from latest file. Here USER_ID is the primary key, how to find out for which user which is the last updated file and display those records?

Below is my code

for each file in 'Documents\all_transfer_event_users-????-??-??-??-??-??.csv'

MASTER_TABLE:
LOAD distinct *
//left(FileName(),10) as FILE_DATE
from [$(file)]
(txt, utf8, embedded labels, delimiter is ',', msq)
;

Concatenate(MASTER_TABLE)

LOAD distinct *
//left(FileName(),10) as FILE_DATE
from [$(file)]
(txt, utf8, embedded labels, delimiter is ',', msq)
;

[martinpohl]

use this

max({<USER_ID={"=count(FILE_DATE)>1"}>} FILE_DATE)

to show the newest FILE_DATE only for USER_ID that have more than one entry

Reagds

[ManiSK]

Hi Martin,

Actually i'm not displaying in the dashboard. Generating the report and storing file  csv format.

so the report should contains latest record for those 5 columns alone.

[sujit_nath]

Suppose You have A---Z columns and A,B,C causing duplicates. I believe loading them separately can solve the problem.

MASTER_TABLE:
LOAD distinct 

USER_ID, D , E ,F...............Z
from [$(file)]
(txt, utf8, embedded labels, delimiter is ',', msq)
;

Concatenate(MASTER_TABLE)

LOAD distinct 

USER_ID, D , E ,F...............Z
from [$(file)]
(txt, utf8, embedded labels, delimiter is ',', msq)
;

LEFT JOIN(MASTER_TABLE)

LOAD distinct 

USER_ID, A , B , C
from [$(file)]
(txt, utf8, embedded labels, delimiter is ',', msq)
;

Concatenate(MASTER_TABLE)

LOAD distinct 

USER_ID, A , B , C
from [$(file)]
(txt, utf8, embedded labels, delimiter is ',', msq)
;