Skip to main content

Qlik

cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Woohoo! Qlik Community has won “Best in Class Community” in the 2024 Khoros Kudos awards!
Announcements
Nov. 20th, Qlik Insider - Lakehouses: Driving the Future of Data & AI - PICK A SESSION
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
fashid
Specialist
Specialist
‎2016-09-16 02:36 PM

Scenario based fetching of records based on date

Hi Qlik Experts,

I have a scenario with which i need a little help . After several operations on the base data i have created this table which is in the excel file.

From this table i need to fetch those application no's whose computerized tread mill test is issued after the ECG test.

For ECG test i have min_date_1 column and for computerized tread mill test i have min_date_2 column

How do i go about doing this ?

Regards,

Nadeem

test.qvw
1604 KB
490 Views
0 Likes
1 Solution

Accepted Solutions
jagan
Luminary Alumni
Luminary Alumni
‎2016-09-19 01:11 AM

Hi,

Try like this

TEMP:

LOAD APPLICATION_NO_1,

     min_date_1,

     min_date_2,

     REQUIREMENT_DESC_1

FROM

TB12_20160916_204425.csv

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Temp1:

LOAD

APPLICATION_NO_1,

Date1,

Date2

WHERE Flag = 1;

LOAD

If(Max(min_date_1) < Min(min_date_2), 1, 0) AS Flag,

APPLICATION_NO_1,

     Date(Max(min_date_1)) AS Date1,

     Date(Min(min_date_2)) AS Date2

RESIDENT TEMP

GROUP BY APPLICATION_NO_1;

View solution in original post

382 Views
0 Likes
4 Replies
swuehl
MVP
MVP
‎2016-09-16 04:31 PM

maybe like

=Aggr(If(Max(min_date_1)<Min(min_date_2), APPLICATION_NO_1),APPLICATION_NO_1) Date(Max(min_date_1)) Date(Min(min_date_2))
17-08-2016 26-07-2015
110000449509024-07-201526-07-2015
110000943080425-09-201529-09-2015
110000954823729-09-201508-10-2015
110000958787803-10-201507-10-2015
110000958804903-10-201517-10-2015
110000959283703-10-201520-10-2015
110001047360714-10-201515-10-2015
110001090065015-10-201512-12-2015
110001132958118-10-201508-11-2015
110001141091420-10-201530-11-2015
110001253277024-10-201525-10-2015
110001754921205-11-201506-11-2015
110001758648513-11-201528-11-2015
110001791358509-11-201509-01-2016
110001923864716-11-201514-12-2015
110002069708023-11-201530-11-2015
110002196428420-11-201521-11-2015
110002272359023-11-201526-12-2015
110002295458023-11-201504-01-2016
110002346908724-11-201525-11-2015
110002389258225-11-201530-12-2015
110002472158028-11-201515-12-2015
110002496508026-11-201511-01-2016
110002496708026-11-201527-11-2015
110002676608029-11-201517-12-2015
110002795008001-12-201502-12-2015
110002795609601-12-201519-01-2016
110002800811601-12-201517-12-2015
110002828861503-12-201502-01-2016
110002828864003-12-201513-12-2015
110002845258108-12-201508-01-2016
110002861608604-12-201520-01-2016
110002917208507-12-201508-12-2015
110002927762907-12-201530-01-2016
110002946289008-12-201509-12-2015
110003008414009-12-201526-12-2015
110003019108009-12-201514-12-2015
110003044658322-12-201509-01-2016
110003053017410-12-201504-01-2016
110003061868710-12-201512-12-2015
110003072408017-12-201530-12-2015
110003154269114-12-201528-12-2015
110003254505326-12-201531-12-2015
110003260826822-12-201524-12-2015
110003311060921-12-201522-12-2015
110003311139521-12-201505-01-2016
110003311231721-12-201512-01-2016
110003312399322-12-201524-12-2015
110003314571622-12-201524-12-2015
110003340222222-12-201524-12-2015
110003399174523-12-201526-12-2015
110003399516323-12-201501-01-2016
110003409338023-12-201504-01-2016
110003417871824-12-201528-12-2015
110003420321605-01-201607-01-2016
110003444573124-12-201529-12-2015
110003582274328-12-201529-12-2015
110003622845429-12-201507-01-2016
110003622879529-12-201511-01-2016
110003623230029-12-201531-12-2015
110003630531730-12-201512-01-2016
110003703933931-12-201505-01-2016
110003885384905-01-201608-01-2016
110003917257607-01-201629-01-2016
110003939024307-01-201606-02-2016
110003940478709-01-201611-01-2016
110003941258010-01-201613-01-2016
110003941483711-01-201619-02-2016
110003941561211-01-201613-01-2016
110003941591411-01-201612-01-2016
110003943545921-01-201608-02-2016
110003943565714-01-201615-01-2016
110003943635614-01-201615-01-2016
110003943653014-01-201615-01-2016
110003945349219-01-201620-01-2016
110003945696720-01-201604-02-2016
110003945985220-01-201621-01-2016
110003946502421-01-201622-01-2016
110003946906022-01-201624-02-2016
110003947808601-02-201619-02-2016
110003948215225-01-201604-02-2016
110003949546029-01-201625-02-2016
110003950481730-01-201617-02-2016
110003950995401-02-201605-02-2016
110003951033601-02-201611-02-2016
110003951125004-02-201624-03-2016
110003951340204-02-201616-02-2016
110003952172805-02-201622-02-2016
110003952664605-02-201606-02-2016
110003953618408-02-201609-02-2016
110003953669808-02-201627-02-2016
110003954411709-02-201610-02-2016
110003954822516-02-201619-02-2016
110003955050513-02-201625-02-2016
110003955183811-02-201613-02-2016
110003955624512-02-201613-02-2016
110003955827813-02-201615-02-2016
110003957360610-03-201619-03-2016
110003957795920-02-201623-02-2016
110003958369222-02-201623-02-2016
110003959109524-02-201625-02-2016
110003959343124-02-201628-02-2016
110003959755225-02-201627-02-2016
110003960514027-02-201628-02-2016
110003960600927-02-201603-04-2016
110003960877429-02-201603-03-2016
110003961664301-03-201602-03-2016
110003962643609-03-201621-03-2016
110003963175607-03-201612-03-2016
110003963259607-03-201609-03-2016
110003964102312-03-201615-03-2016
110003965929014-03-201629-03-2016
110003966365115-03-201630-03-2016
110003967698418-03-201605-04-2016
110003968266419-03-201623-03-2016
110003969947623-03-201616-04-2016
110003970181724-03-201628-03-2016
110003970396625-03-201628-03-2016
110003970428425-03-201627-03-2016
110003971235730-03-201618-05-2016
110003971380629-03-201618-04-2016
110003972448430-03-201622-04-2016
110003972459930-03-201601-04-2016
110003973150230-03-201603-04-2016
110003973623504-04-201614-05-2016
110003973868931-03-201604-04-2016
110003974297231-03-201623-04-2016
110003974396131-03-201604-04-2016
110003974507231-03-201604-04-2016
110003977686412-04-201613-04-2016
110003978212714-04-201604-05-2016
110003983525902-05-201603-05-2016
110003983741802-05-201607-05-2016
110003984796605-05-201622-07-2016
110003989043318-05-201619-05-2016
110003989103821-05-201614-06-2016
110003990503923-05-201625-05-2016
110003992107330-05-201631-05-2016
110003992772315-06-201607-07-2016
110003997095607-06-201627-06-2016
110003997224907-06-201608-06-2016
110003997628108-06-201616-06-2016
110003997967909-06-201617-06-2016
110003998810517-06-201626-06-2016
110004008672801-07-201605-07-2016
110004010342007-07-201613-07-2016
110004018248918-07-201621-07-2016
110004018980620-07-201621-07-2016
110004024009330-07-201631-07-2016
110004027958105-08-201606-08-2016
120004007220729-06-201601-07-2016
120004009381502-07-201604-07-2016
120004016268114-07-201615-07-2016
120004018107118-07-201628-07-2016
120004019043920-07-201621-07-2016
120004020998725-07-201628-07-2016
130032061539703-03-201608-03-2016
130091082335726-07-201605-08-2016
130094066788911-04-201626-04-2016
130288081427619-07-201620-07-2016
130362063148514-03-201630-03-2016
130421060136622-02-201625-03-2016
130540069221325-04-201626-04-2016
130673059160415-02-201605-03-2016
130997058282009-02-201623-02-2016
131362063746318-03-201621-03-2016
131409058556811-02-201628-02-2016
131426085618017-08-201630-08-2016
131900065615331-03-201604-04-2016
131927078440928-06-201609-07-2016
132183064024220-03-201622-03-2016
132274064747825-03-201606-04-2016
132365063165614-03-201625-03-2016
132375075425708-06-201609-06-2016
133035063752518-03-201619-04-2016
133214061179929-02-201606-04-2016
133277064052720-03-201603-04-2016
133410062579510-03-201616-04-2016
133753067197622-04-201614-05-2016
133811063174114-03-201629-03-2016
133812060854127-02-201629-02-2016
133882053380206-01-201624-02-2016
133998062556310-03-201611-03-2016
134041069225525-04-201626-04-2016
134044065350230-03-201631-03-2016
134128074849603-06-201604-06-2016
134180061153029-02-201602-03-2016
134182070462004-05-201618-05-2016
134433076673816-06-201617-06-2016
134717058701812-02-201622-02-2016
134820074420231-05-201601-06-2016
134833065448130-03-201621-04-2016
134905074536801-06-201623-06-2016
135028075558808-06-201609-06-2016
135056058553811-02-201612-02-2016
135089065198028-03-201618-04-2016
135601064146422-03-201603-04-2016
136038078406128-06-201629-06-2016
136115057531304-02-201616-02-2016
136426076262813-06-201609-07-2016
136614054539714-01-201625-01-2016
136717065447830-03-201601-04-2016
136876083748405-08-201619-08-2016
136956078851101-07-201619-07-2016
136962078734530-06-201604-07-2016
137052063331615-03-201618-03-2016
137074074552601-06-201602-06-2016
137109065189228-03-201604-04-2016
137541065180228-03-201629-03-2016
137743060725226-02-201628-02-2016
137781073684226-05-201627-05-2016
137908063429816-03-201625-04-2016
138193065332229-03-201630-03-2016
138233065475304-04-201622-04-2016
138370067483113-04-201629-04-2016
138424084474609-08-201623-08-2016
138482061153329-02-201612-03-2016
138532063601517-03-201618-03-2016
138661059276916-02-201617-02-2016
138673066178704-04-201612-04-2016
138680069944709-05-201626-05-2016
138696076711416-06-201617-06-2016
138754074381631-05-201622-06-2016
138868074869003-06-201604-06-2016
139198058709813-02-201627-02-2016
139265058057408-02-201609-02-2016
139271061303501-03-201609-03-2016
139383062911214-03-201630-03-2016
139385054251912-01-201613-01-2016
139466063467321-03-201602-04-2016
139478084609610-08-201612-08-2016
139605071681612-05-201613-05-2016
139714058364510-02-201623-02-2016
139715081579520-07-201622-07-2016
139831057690005-02-201609-02-2016
test (43).qvw
382 Views
fashid
Specialist
Specialist
‎2016-09-19 12:54 AM
Author

In response to swuehl

Hi Stefan,

Thank you for your reply , i am sorry i did not mention that i wanted to do this in the scripting itself and create a table with all those application numbers.

Regards,

Nadeem

382 Views
0 Likes
jagan
Luminary Alumni
Luminary Alumni
‎2016-09-19 01:11 AM

Hi,

Try like this

TEMP:

LOAD APPLICATION_NO_1,

     min_date_1,

     min_date_2,

     REQUIREMENT_DESC_1

FROM

TB12_20160916_204425.csv

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Temp1:

LOAD

APPLICATION_NO_1,

Date1,

Date2

WHERE Flag = 1;

LOAD

If(Max(min_date_1) < Min(min_date_2), 1, 0) AS Flag,

APPLICATION_NO_1,

     Date(Max(min_date_1)) AS Date1,

     Date(Min(min_date_2)) AS Date2

RESIDENT TEMP

GROUP BY APPLICATION_NO_1;

383 Views
0 Likes
fashid
Specialist
Specialist
‎2016-09-22 01:29 AM
Author

Sorry for the late response but both the solutions are excellent and worked fine for me .

wish i could mark both of them correct .

Thanks for all your help. really appreciate.

382 Views
0 Likes