Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Highlighted
kanalavs
New 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
MVP
MVP

Re: Update Min date to corresponding file no

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
pritamb89
Contributor

Re: Update Min date to corresponding file no

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
Contributor II

Re: Update Min date to corresponding file no

Hi, 

Please see my solution by using 

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

Untitled.jpg

Hope this helps

ZZ

shiveshsingh
Honored Contributor

Re: Update Min date to corresponding file no

Short and crisp

 

Perfect Smiley Happy

Community Browser