topic Re: Update Min date to corresponding file no in QlikView

Update Min date to corresponding file no

kanalavs — Fri, 11 Jan 2019 04:45:49 GMT

Hi Friends,

Please help, how to update min date to the corresponding file no. The case is as follows:

The below is the set of records for one person with two file numbers which are created with different dates, please refer pic1. The requirement is to consider only min date for the two records. Please see pic2 for the expected result:

Pic1: source data

Created Date	FILE_NO	PERSON_NAME	PERSON_NO
23/09/2009	IF 2009/0381/000	XXXXXNAME1	XXXXNO1
13/02/2010	IF 2010/0381/001	XXXXXNAME1	XXXXNO1

Pic2: expected result

Created Date	FILE_NO	PERSON_NAME	PERSON_NO
23/09/2009	IF 2009/0381/000	XXXXXNAME1	XXXXNO1
23/09/2009	IF 2010/0381/001	XXXXXNAME1	XXXXNO1

Source Records

Created Date	FILE_NO	PERSON_NAME	PERSON_NO
23/09/2009	IF 2009/0381/000	XXXXXNAME1	XXXXNO1
13/02/2010	IF 2010/0381/001	XXXXXNAME1	XXXXNO1
3/2/2010	IF 2010/0461/000	XXXXXNAME2	XXXXNO2
7/1/2011	IF 2011/0461/001	XXXXXNAME2	XXXXNO2
3/2/2010	IF 2010/0581/000	XXXXXNAME3	XXXXNO3
7/1/2011	IF 2011/0581/001	XXXXXNAME3	XXXXNO3
3/2/2010	IF 2010/0681/000	XXXXXNAME4	XXXXNO4
7/1/2011	IF 2011/0681/001	XXXXXNAME4	XXXXNO4
3/2/2010	IF 2010/0781/000	XXXXXNAME5	XXXXNO5
7/1/2011	IF 2011/0781/001	XXXXXNAME5	XXXXNO5

Re: Update Min date to corresponding file no

jonathandienst — Fri, 11 Jan 2019 05:14:44 GMT

In a table with PERSON_NAME as dimension, use the expression:

Min(TOTAL <PERSON_NAME> [Created Date])

Re: Update Min date to corresponding file no

jerryyang756 — Fri, 11 Jan 2019 06:15:42 GMT

You can do the same in Load Script

Tab1:
NoConcatenate
LOAD
Date([Created Date],'DD/MM/YYYY') as Created_Date,
FILE_NO,
PERSON_NAME,
PERSON_NO
FROM
[C:\Users\Qvd.pritam\Desktop\Book1.xlsx]
(ooxml, embedded labels, table is Sheet2);

Caltemp:
NoConcatenate
LOAD
MIN(Date(Created_Date,'DD/MM/YYYY')) AS MinDate
RESIDENT Tab1;
Drop Table Tab1;

LET vMinnDate = peek('MinDate', 0, 'Caltemp');
Drop Table Caltemp;

Tab2:
NoConcatenate
LOAD
'$(vMinnDate)' as 'Created Date',
FILE_NO,
PERSON_NAME,
PERSON_NO
FROM
[C:\Users\Qvd.pritam\Desktop\Book1.xlsx]
(ooxml, embedded labels, table is Sheet2);

Here is the output

Re: Update Min date to corresponding file no

zzyjordan — Fri, 11 Jan 2019 06:17:46 GMT

Hi,

Please see my solution by using

=Aggr(min([Created Date]),PERSON_NAME)

Hope this helps

Re: Update Min date to corresponding file no

shiveshsingh — Fri, 11 Jan 2019 06:59:48 GMT

Short and crisp

Perfect 🙂