topic Re: Exclude a list from Load script in QlikView

Exclude a list from Load script

Tue, 11 Jun 2013 03:58:10 GMT

Hi,

Hope you could help.

How do I exclude a certain list of IDs from loading?

I have the following script:

LOAD [Id],

[TimeStamp],

[Total Count],

[Info1]

[Info2]

[New ID]

FROM

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

I would like to exclude a list of [New ID], stored in C:\newID.csv]

THe file contains about 3million records, not "," delimited. Just a list of IDs.

Could you pls help? THanks in advance.

Re: Exclude a list from Load script

tresB — Tue, 11 Jun 2013 04:18:21 GMT

a quick idea (may be a better one is there) :

Temp:

LOAD [Id],

[TimeStamp],

[Total Count],

[Info1]

[Info2]

[New ID]

FROM

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

LEFT Join

Load [New ID] as [Id],

'Y' as Flag

From C:\newID.csv;

Final:

Load *,

'1' as Jnk

Resident Temp WHERE Flag<>'Y' ;

Drop Table Temp;

Drop Field Jnk, Flag;

Hope this helps.

Re: Exclude a list from Load script

tresB — Tue, 11 Jun 2013 04:53:02 GMT

Sorry, you have to use LEFT JOIN instead of join.

Re: Exclude a list from Load script

Tue, 11 Jun 2013 07:17:23 GMT

I have 2 IDs in my original file, [id] and [New ID]. how do i left join?

I would like to load the data where [NewID] in source file is same as [NewID] in my new criteria list.

Also, what is the flag?

Re: Exclude a list from Load script

tresB — Tue, 11 Jun 2013 07:27:05 GMT

i should have noticed that. please find the changes in the earlier post in bold.

Re: Exclude a list from Load script

Tue, 11 Jun 2013 08:34:47 GMT

Load [New ID] as [Id],

'Y' as Flag

From C:\newID.csv;

Final:

Load *,

'1' as Jnk

Resident Temp WHERE Flag<>'Y' ;

Drop Table Temp;

Drop Field Jnk, Flag;

Q1: Do i use join or left join?

Q2: I do not have Flag field in my file. all i have is a list of new id, not separated by commas.\

How should the script be like?

Re: Exclude a list from Load script

Tue, 11 Jun 2013 08:52:20 GMT

To be sure, the newID field in main file, is combined from the ID+Timestamp

Directory;

Temp:

LOAD [Id],

[TimeStamp],

[Total Count],

[Info1]

[Info2]

[Id] &'-'& [Timestamp] as [New ID]

FROM

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

Load [New ID]

'Y' as Flag

From C:\newID.csv;

Final:

Load *,

'1' as Jnk

Resident Temp WHERE Flag<>'Y' ;

Drop Table Temp;

Drop Field Jnk, Flag;

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

I encounter this error:

Field not found - NewID

LEFT Join

Load imsiTime,

'Y' as Flag

From

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

To be sure, the formats are as below:

Source file:

Id	Timestamp	Total Count	Info1	Info2
1	8am
2	8am
3	8am

NewID

1-8am

2-8am

3-8am

My final file should only output 1-8am and the parameters

Re: Exclude a list from Load script

Tue, 11 Jun 2013 10:28:51 GMT

I think i have solved my join statement.

Now i have this error:

Table not found

FINAL:

Load *,

'1' as Jnk

Resident Temp WHERE Flag<>'Y'

Can someone pls help?

Re: Exclude a list from Load script

Tue, 11 Jun 2013 12:33:44 GMT

What about Where Not Exists.

Please see attached Example.

Hope this helps.

Re: Exclude a list from Load script

Wed, 12 Jun 2013 05:44:19 GMT

is it possible to copy the script?

So sorry because i am using a personal edition.

Re: Exclude a list from Load script

tresB — Wed, 12 Jun 2013 06:22:33 GMT

Could you please put your script here or share your application? I believe you are getting the "Table not found" error due to some misplacement of JOIN statement or so(probably the Temp table is getting auto concatenated with some earlier table), otherwise the script i posted would do the job for you.

Thanks.

Re: Exclude a list from Load script

Wed, 12 Jun 2013 08:16:10 GMT

TempTable:

Load * Inline

[

temp_id

];

Data:

Load *

Where Not Exists (temp_id,id)

;

Load * Inline

[

id, Sales

1, 10

2, 20

3, 30

4, 40

5, 50

6, 60

7, 70

8, 80

9, 90

10,100

];

Drop Table TempTable;

Re: Exclude a list from Load script

Fri, 14 Jun 2013 02:19:44 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);

Inner Join

LOAD timePersonel

FROM

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

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

With the script above,

error i encounter says:

Field not found - <timePersonnel>

Inner Join

LOAD timePersonnel

FROM

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

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

Changing 2nd part of my code to

LOAD @1

FROM

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

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

it just hangs

Can someone help me here?

thanks!

Re: Exclude a list from Load script

tresB — Fri, 14 Jun 2013 04:14:07 GMT

Changing 2nd part of my code to
LOAD @1
FROM
(txt, utf8, no labels, delimiter is ',', msq);
------------------------
it just hangs

Write this portion as

Load @1 as timePersonnel

FROM

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

It should work.

Thanks.

Re: Exclude a list from Load script

Fri, 14 Jun 2013 07:03:56 GMT

It still seems to hang... if i have about 5 million lines... will it take a long time to load and will it casue problems? I have been waiting for about 10 min.

Re: Exclude a list from Load script

tresB — Fri, 14 Jun 2013 07:29:31 GMT

Try 'Limited Load' under Debug window. Then you can figure out if the data size is the issue or not.