Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
kanalavs
Contributor
Contributor

Update Min date to corresponding file no

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 DateFILE_NOPERSON_NAMEPERSON_NO
23/09/2009IF 2009/0381/000XXXXXNAME1XXXXNO1
13/02/2010IF 2010/0381/001XXXXXNAME1XXXXNO1

 

Pic2: expected result

Created DateFILE_NOPERSON_NAMEPERSON_NO
23/09/2009IF 2009/0381/000XXXXXNAME1XXXXNO1
23/09/2009IF 2010/0381/001XXXXXNAME1XXXXNO1

 

Source Records

Created DateFILE_NOPERSON_NAMEPERSON_NO
23/09/2009IF 2009/0381/000XXXXXNAME1XXXXNO1
13/02/2010IF 2010/0381/001XXXXXNAME1XXXXNO1
3/2/2010IF 2010/0461/000XXXXXNAME2XXXXNO2
7/1/2011IF 2011/0461/001XXXXXNAME2XXXXNO2
3/2/2010IF 2010/0581/000XXXXXNAME3XXXXNO3
7/1/2011IF 2011/0581/001XXXXXNAME3XXXXNO3
3/2/2010IF 2010/0681/000XXXXXNAME4XXXXNO4
7/1/2011IF 2011/0681/001XXXXXNAME4XXXXNO4
3/2/2010IF 2010/0781/000XXXXXNAME5XXXXNO5
7/1/2011IF 2011/0781/001XXXXXNAME5XXXXNO5
4 Replies
jonathandienst
Partner - Champion III
Partner - Champion III

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

Min(TOTAL <PERSON_NAME> [Created Date])
Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
jerryyang756
Creator
Creator

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

mindate.PNG

zzyjordan
Creator II
Creator II

Hi, 

Please see my solution by using 

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

Untitled.jpg

Hope this helps

ZZ

shiveshsingh
Master
Master

Short and crisp

 

Perfect 🙂