topic Re: Where not exist in autoconcatenation in App Development

Where not exist in autoconcatenation

stonecold111 — Tue, 30 Mar 2021 09:40:48 GMT

Hi qlikss,

I have 2 files in folder. Names of files.

I) daily_ 1.txt

Ii) daily_2.txt

I am loading into qliksense by using *

Load * from daily_*.txt.

When I load I want to check whether record exist in first table by using id field in tables.

How can I do this when I am loading like this.

Re: Where not exist in autoconcatenation

stonecold111 — Tue, 30 Mar 2021 09:50:26 GMT

@QFabian

@Taoufiq_Zarra

Re: Where not exist in autoconcatenation

stonecold111 — Tue, 30 Mar 2021 10:21:46 GMT

Sample table:

Daily_1

I'd. Value

1. 100

Daily_2

I'd. Value

1 200

2. 300

Final table

I'd. Value

1. 100

2. 300

Re: Where not exist in autoconcatenation

rwunderlich — Tue, 30 Mar 2021 21:49:38 GMT

Have you tried:

Load * from daily_*.txt
Where not Exists(id);

-Rob

Re: Where not exist in autoconcatenation

stonecold111 — Wed, 31 Mar 2021 08:11:18 GMT

Thanks Rob.

Re: Where not exist in autoconcatenation

stonecold111 — Thu, 08 Apr 2021 05:28:18 GMT

Hi Bob,

It's working fine for where not exists ,

But when I use where exists it's fetching 0 records

It should be

Final table

1. 100

1. 200

Re: Where not exist in autoconcatenation

stonecold111 — Thu, 08 Apr 2021 08:27:36 GMT

@rwunderlich

@QFabian

Re: Where not exist in autoconcatenation

rwunderlich — Thu, 08 Apr 2021 14:44:48 GMT

If you use

Where exists(id)

for every load nothing will get loaded. Because nothing has been loaded yet, the count of existing ids is zero.

-Rob

Re: Where not exist in autoconcatenation

QFabian — Thu, 08 Apr 2021 14:50:09 GMT

Hi @stonecold111 , here i made this sample, please check it :

Daily_1:
Load * INLINE [
Id1, Value
1,100
];

Daily_2:
Load * INLINE [
Id2, Value
1, 200
2, 300
];

Final:
Load
Id1 as ID,
Value
Resident Daily_1;

Load
Id2 as ID,
Value
Resident Daily_2
Where not Exists(ID, Id2);

drop tables
Daily_1, Daily_2

Re: Where not exist in autoconcatenation

stonecold111 — Fri, 09 Apr 2021 05:37:43 GMT

Hi Fabian,

I provided sample data . I have to auto concatenate

5 Excel files. I need to take only IDs from latest file and need to check whether same I'd s are there from previous files.

So, final table

All IDs from latest file.+ Any matching IDs from previous 9 files.

Example:

In folder I have 2 files.

First excel copied to this folder at 10 am

Data in first excel

I'd. Name

1. 100

5. 500

Second excel file copied into this folder at 11 am

Data in second excel

1 200

6 600

When I refresh at 10.30 am

My output is

I'd. Name

1. 100

5. 500

When I refresh at 11.30 am

1. 200

1. 100

5. 500

Like this in a day 5 Excel files are going to add to this folder at different intervals. I have multiple refreshes for this.

Re: Where not exist in autoconcatenation

stonecold111 — Fri, 09 Apr 2021 06:53:14 GMT

Small correction

When I refresh at 11.30 am

Final table

1. 100

1. 200

6. 600

Re: Where not exist in autoconcatenation

rwunderlich — Fri, 09 Apr 2021 15:16:31 GMT

You want the latest id row. Use "where not exists(id) " but load the files ordered latest file first. You can build the list using "for each x in FileList()" or "First 1" like shown below. Here's an sample:

Files:
First 1
LOAD
FilePath() as FilePath,
FileTime() as FileTime
FROM
daily_*.txt
(fix, utf8, no labels);

Files2:
NoConcatenate
LOAD *
Resident Files
Order By FileTime desc;

Drop Table Files;

For i = 0 to NoOfRows('Files2')
LET vFile = Peek('FilePath', RecNo(), 'Files2');
Data:
LOAD *
FROM
[$(vFile)] (your filespec...
Where not exists(id)
;
Next i

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com