topic Re: Exclude file 2 from file 1 in QlikView

Exclude file 2 from file 1

Mon, 17 Jun 2013 01:20:27 GMT

File1 has a few million records. timePersonnel is Time +Personnel

Time Stamp	Personnel Id	Info1	Info2	timePersonnel
08:00	1001	test1001	test2001	08:00-1001
08:00	1002	test1002	test2002	08:00-1002
08:00	1003	test1003	test2003	08:00-1003
08:00	1004	test1004	test2004	08:00-1004
08:00	1005	test1005	test2005	08:00-1005
09:00	1001	abc1001	abc2001	09:00-1001

File2.csv has the following content

timePersonnel

08:00-1001

08:00-1002

08:00-1003

09:00-1001

I would like to obtain the results similar to the below.

Time Stamp	Personnel Id	Info1	Info2	timePersonnel
08:00	1001	test1001	test2001	08:00-1001
08:00	1002	test1002	test2002	08:00-1002
08:00	1003	test1003	test2003	08:00-1003
08:00	1004	test1004	test2004	08:00-1004
08:00	1005	test1005	test2005	08:00-1005
09:00	1001	abc1001	abc2001	09:00-1001

------------------------

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='D/M/YYYY';

SET TimestampFormat='D/M/YYYY h:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

Directory;

LOAD [Time Stamp],

[Personnel Id],

Info1,

Info2,

timePersonnel

FROM

(txt, utf8, embedded labels, delimiter is '\t', msq);

Where not exists

LOAD timePersonel

FROM

(txt, utf8, no labels, delimiter is ',', msq);

------------------------

I would like to exclude the items that are found in file2. I read somewhere about "where not exists". but it does not seem to work. Can i know how to exclude the IDs found in file2 in the final output? Previouls i have asked about inner join, to output the items found in file2.

thanks.

Re: Exclude file 2 from file 1

Gysbert_Wassenaar — Mon, 17 Jun 2013 08:00:54 GMT

Try it the other way around:

T1:

LOAD timePersonel

FROM

(txt, utf8, no labels, delimiter is ',', msq);

T2:

LOAD [Time Stamp],

[Personnel Id],

Info1,

Info2,

timePersonnel

FROM

(txt, utf8, embedded labels, delimiter is '\t', msq)

where not exists(timePersonnel);

// optional:

// drop table T1;

Re: Exclude file 2 from file 1

Tue, 18 Jun 2013 01:23:07 GMT

Can someone let me know what i have missed out?

I have encountered the following error, and load fails. Did i do something wrong?

Thanks a lot.

-----------

Field not found - <timePesonnel>

T2:

LOAD [Time Stamp],

[Personnel Id],

Info1,

Info2,

timePersonnel

FROM

(txt, codepage is 936, embedded labels, delimiter is ',', msq)

where not exists (timePesonnel)

-----------

My script reads like this:

-----------

Directory;

T1:

LOAD timePersonnel

FROM

(txt, codepage is 936, embedded labels, delimiter is '\t', msq);

T2:

LOAD [Time Stamp],

[Personnel Id],

Info1,

Info2,

timePersonnel

FROM

(txt, codepage is 936, embedded labels, delimiter is ',', msq)

where not exists (timePesonnel);

Re: Exclude file 2 from file 1

Wed, 19 Jun 2013 06:11:34 GMT

Can someone advise? Apologies that this might be a simple question.

Re: Exclude file 2 from file 1

Wed, 19 Jun 2013 06:15:56 GMT

Find attachment It may help you..

If you need clarification reply me..

Karthik

Re: Exclude file 2 from file 1

Gysbert_Wassenaar — Wed, 19 Jun 2013 17:29:58 GMT

Field not found - <timePesonnel>

Typo: timePesonnel instead of timePersonnel