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

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

ManiSK — Tue, 31 Mar 2020 07:17:06 GMT

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

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

martinpohl — Tue, 31 Mar 2020 07:53:14 GMT

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

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

ManiSK — Tue, 31 Mar 2020 07:58:53 GMT

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.

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

sujit_nath — Tue, 31 Mar 2020 13:23:11 GMT

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