Solved: Re: Scenario based fetching of records based on da... - Qlik Community

fashid · ‎2016-09-16

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

jagan · ‎2016-09-19

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

swuehl · ‎2016-09-16

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
1100004495090	24-07-2015	26-07-2015
1100009430804	25-09-2015	29-09-2015
1100009548237	29-09-2015	08-10-2015
1100009587878	03-10-2015	07-10-2015
1100009588049	03-10-2015	17-10-2015
1100009592837	03-10-2015	20-10-2015
1100010473607	14-10-2015	15-10-2015
1100010900650	15-10-2015	12-12-2015
1100011329581	18-10-2015	08-11-2015
1100011410914	20-10-2015	30-11-2015
1100012532770	24-10-2015	25-10-2015
1100017549212	05-11-2015	06-11-2015
1100017586485	13-11-2015	28-11-2015
1100017913585	09-11-2015	09-01-2016
1100019238647	16-11-2015	14-12-2015
1100020697080	23-11-2015	30-11-2015
1100021964284	20-11-2015	21-11-2015
1100022723590	23-11-2015	26-12-2015
1100022954580	23-11-2015	04-01-2016
1100023469087	24-11-2015	25-11-2015
1100023892582	25-11-2015	30-12-2015
1100024721580	28-11-2015	15-12-2015
1100024965080	26-11-2015	11-01-2016
1100024967080	26-11-2015	27-11-2015
1100026766080	29-11-2015	17-12-2015
1100027950080	01-12-2015	02-12-2015
1100027956096	01-12-2015	19-01-2016
1100028008116	01-12-2015	17-12-2015
1100028288615	03-12-2015	02-01-2016
1100028288640	03-12-2015	13-12-2015
1100028452581	08-12-2015	08-01-2016
1100028616086	04-12-2015	20-01-2016
1100029172085	07-12-2015	08-12-2015
1100029277629	07-12-2015	30-01-2016
1100029462890	08-12-2015	09-12-2015
1100030084140	09-12-2015	26-12-2015
1100030191080	09-12-2015	14-12-2015
1100030446583	22-12-2015	09-01-2016
1100030530174	10-12-2015	04-01-2016
1100030618687	10-12-2015	12-12-2015
1100030724080	17-12-2015	30-12-2015
1100031542691	14-12-2015	28-12-2015
1100032545053	26-12-2015	31-12-2015
1100032608268	22-12-2015	24-12-2015
1100033110609	21-12-2015	22-12-2015
1100033111395	21-12-2015	05-01-2016
1100033112317	21-12-2015	12-01-2016
1100033123993	22-12-2015	24-12-2015
1100033145716	22-12-2015	24-12-2015
1100033402222	22-12-2015	24-12-2015
1100033991745	23-12-2015	26-12-2015
1100033995163	23-12-2015	01-01-2016
1100034093380	23-12-2015	04-01-2016
1100034178718	24-12-2015	28-12-2015
1100034203216	05-01-2016	07-01-2016
1100034445731	24-12-2015	29-12-2015
1100035822743	28-12-2015	29-12-2015
1100036228454	29-12-2015	07-01-2016
1100036228795	29-12-2015	11-01-2016
1100036232300	29-12-2015	31-12-2015
1100036305317	30-12-2015	12-01-2016
1100037039339	31-12-2015	05-01-2016
1100038853849	05-01-2016	08-01-2016
1100039172576	07-01-2016	29-01-2016
1100039390243	07-01-2016	06-02-2016
1100039404787	09-01-2016	11-01-2016
1100039412580	10-01-2016	13-01-2016
1100039414837	11-01-2016	19-02-2016
1100039415612	11-01-2016	13-01-2016
1100039415914	11-01-2016	12-01-2016
1100039435459	21-01-2016	08-02-2016
1100039435657	14-01-2016	15-01-2016
1100039436356	14-01-2016	15-01-2016
1100039436530	14-01-2016	15-01-2016
1100039453492	19-01-2016	20-01-2016
1100039456967	20-01-2016	04-02-2016
1100039459852	20-01-2016	21-01-2016
1100039465024	21-01-2016	22-01-2016
1100039469060	22-01-2016	24-02-2016
1100039478086	01-02-2016	19-02-2016
1100039482152	25-01-2016	04-02-2016
1100039495460	29-01-2016	25-02-2016
1100039504817	30-01-2016	17-02-2016
1100039509954	01-02-2016	05-02-2016
1100039510336	01-02-2016	11-02-2016
1100039511250	04-02-2016	24-03-2016
1100039513402	04-02-2016	16-02-2016
1100039521728	05-02-2016	22-02-2016
1100039526646	05-02-2016	06-02-2016
1100039536184	08-02-2016	09-02-2016
1100039536698	08-02-2016	27-02-2016
1100039544117	09-02-2016	10-02-2016
1100039548225	16-02-2016	19-02-2016
1100039550505	13-02-2016	25-02-2016
1100039551838	11-02-2016	13-02-2016
1100039556245	12-02-2016	13-02-2016
1100039558278	13-02-2016	15-02-2016
1100039573606	10-03-2016	19-03-2016
1100039577959	20-02-2016	23-02-2016
1100039583692	22-02-2016	23-02-2016
1100039591095	24-02-2016	25-02-2016
1100039593431	24-02-2016	28-02-2016
1100039597552	25-02-2016	27-02-2016
1100039605140	27-02-2016	28-02-2016
1100039606009	27-02-2016	03-04-2016
1100039608774	29-02-2016	03-03-2016
1100039616643	01-03-2016	02-03-2016
1100039626436	09-03-2016	21-03-2016
1100039631756	07-03-2016	12-03-2016
1100039632596	07-03-2016	09-03-2016
1100039641023	12-03-2016	15-03-2016
1100039659290	14-03-2016	29-03-2016
1100039663651	15-03-2016	30-03-2016
1100039676984	18-03-2016	05-04-2016
1100039682664	19-03-2016	23-03-2016
1100039699476	23-03-2016	16-04-2016
1100039701817	24-03-2016	28-03-2016
1100039703966	25-03-2016	28-03-2016
1100039704284	25-03-2016	27-03-2016
1100039712357	30-03-2016	18-05-2016
1100039713806	29-03-2016	18-04-2016
1100039724484	30-03-2016	22-04-2016
1100039724599	30-03-2016	01-04-2016
1100039731502	30-03-2016	03-04-2016
1100039736235	04-04-2016	14-05-2016
1100039738689	31-03-2016	04-04-2016
1100039742972	31-03-2016	23-04-2016
1100039743961	31-03-2016	04-04-2016
1100039745072	31-03-2016	04-04-2016
1100039776864	12-04-2016	13-04-2016
1100039782127	14-04-2016	04-05-2016
1100039835259	02-05-2016	03-05-2016
1100039837418	02-05-2016	07-05-2016
1100039847966	05-05-2016	22-07-2016
1100039890433	18-05-2016	19-05-2016
1100039891038	21-05-2016	14-06-2016
1100039905039	23-05-2016	25-05-2016
1100039921073	30-05-2016	31-05-2016
1100039927723	15-06-2016	07-07-2016
1100039970956	07-06-2016	27-06-2016
1100039972249	07-06-2016	08-06-2016
1100039976281	08-06-2016	16-06-2016
1100039979679	09-06-2016	17-06-2016
1100039988105	17-06-2016	26-06-2016
1100040086728	01-07-2016	05-07-2016
1100040103420	07-07-2016	13-07-2016
1100040182489	18-07-2016	21-07-2016
1100040189806	20-07-2016	21-07-2016
1100040240093	30-07-2016	31-07-2016
1100040279581	05-08-2016	06-08-2016
1200040072207	29-06-2016	01-07-2016
1200040093815	02-07-2016	04-07-2016
1200040162681	14-07-2016	15-07-2016
1200040181071	18-07-2016	28-07-2016
1200040190439	20-07-2016	21-07-2016
1200040209987	25-07-2016	28-07-2016
1300320615397	03-03-2016	08-03-2016
1300910823357	26-07-2016	05-08-2016
1300940667889	11-04-2016	26-04-2016
1302880814276	19-07-2016	20-07-2016
1303620631485	14-03-2016	30-03-2016
1304210601366	22-02-2016	25-03-2016
1305400692213	25-04-2016	26-04-2016
1306730591604	15-02-2016	05-03-2016
1309970582820	09-02-2016	23-02-2016
1313620637463	18-03-2016	21-03-2016
1314090585568	11-02-2016	28-02-2016
1314260856180	17-08-2016	30-08-2016
1319000656153	31-03-2016	04-04-2016
1319270784409	28-06-2016	09-07-2016
1321830640242	20-03-2016	22-03-2016
1322740647478	25-03-2016	06-04-2016
1323650631656	14-03-2016	25-03-2016
1323750754257	08-06-2016	09-06-2016
1330350637525	18-03-2016	19-04-2016
1332140611799	29-02-2016	06-04-2016
1332770640527	20-03-2016	03-04-2016
1334100625795	10-03-2016	16-04-2016
1337530671976	22-04-2016	14-05-2016
1338110631741	14-03-2016	29-03-2016
1338120608541	27-02-2016	29-02-2016
1338820533802	06-01-2016	24-02-2016
1339980625563	10-03-2016	11-03-2016
1340410692255	25-04-2016	26-04-2016
1340440653502	30-03-2016	31-03-2016
1341280748496	03-06-2016	04-06-2016
1341800611530	29-02-2016	02-03-2016
1341820704620	04-05-2016	18-05-2016
1344330766738	16-06-2016	17-06-2016
1347170587018	12-02-2016	22-02-2016
1348200744202	31-05-2016	01-06-2016
1348330654481	30-03-2016	21-04-2016
1349050745368	01-06-2016	23-06-2016
1350280755588	08-06-2016	09-06-2016
1350560585538	11-02-2016	12-02-2016
1350890651980	28-03-2016	18-04-2016
1356010641464	22-03-2016	03-04-2016
1360380784061	28-06-2016	29-06-2016
1361150575313	04-02-2016	16-02-2016
1364260762628	13-06-2016	09-07-2016
1366140545397	14-01-2016	25-01-2016
1367170654478	30-03-2016	01-04-2016
1368760837484	05-08-2016	19-08-2016
1369560788511	01-07-2016	19-07-2016
1369620787345	30-06-2016	04-07-2016
1370520633316	15-03-2016	18-03-2016
1370740745526	01-06-2016	02-06-2016
1371090651892	28-03-2016	04-04-2016
1375410651802	28-03-2016	29-03-2016
1377430607252	26-02-2016	28-02-2016
1377810736842	26-05-2016	27-05-2016
1379080634298	16-03-2016	25-04-2016
1381930653322	29-03-2016	30-03-2016
1382330654753	04-04-2016	22-04-2016
1383700674831	13-04-2016	29-04-2016
1384240844746	09-08-2016	23-08-2016
1384820611533	29-02-2016	12-03-2016
1385320636015	17-03-2016	18-03-2016
1386610592769	16-02-2016	17-02-2016
1386730661787	04-04-2016	12-04-2016
1386800699447	09-05-2016	26-05-2016
1386960767114	16-06-2016	17-06-2016
1387540743816	31-05-2016	22-06-2016
1388680748690	03-06-2016	04-06-2016
1391980587098	13-02-2016	27-02-2016
1392650580574	08-02-2016	09-02-2016
1392710613035	01-03-2016	09-03-2016
1393830629112	14-03-2016	30-03-2016
1393850542519	12-01-2016	13-01-2016
1394660634673	21-03-2016	02-04-2016
1394780846096	10-08-2016	12-08-2016
1396050716816	12-05-2016	13-05-2016
1397140583645	10-02-2016	23-02-2016
1397150815795	20-07-2016	22-07-2016
1398310576900	05-02-2016	09-02-2016

fashid · ‎2016-09-19

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

jagan · ‎2016-09-19

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;

fashid · ‎2016-09-22

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.

Scenario based fetching of records based on date