Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

where condition with other table name in LOAD statment

Hi Everyone

I have two QVD,1.qvd and 2.qvd with the same fields below.
Field:
   Date
   FileName
   ExcutedDate

After I loaded 1.qvd to the TABLE1, I want to load 2.qvd to TABLE2 with the conditions below.
     where not (TABLE1.FileName =TABLE2.FileName AND TABLE1.ExcutedDate=TABLE2.ExcutedDate)

In the script,Table Name cannot be used in the load statment when loading a QVD.
and  I've no idea how to let it work.
Can anyone help me Please. Thank you.

TABLE1:
NoConcatenate LOAD
    Date,
    FileName,
    ExcutedDate
FROM (qvd);

TABLE2:
NoConcatenate LOAD
    Date,
    FileName,
    ExcutedDate
FROM (qvd)
WHERE NOT (TABLE1.FileName =TABLE2.FileName AND TABLE1.ExcutedDate=TABLE2.ExcutedDate);

DROP TABLE TABLE1;

Labels (2)
1 Solution

Accepted Solutions
rahulpawarb
Specialist III
Specialist III

Please try below:

Employees:

LOAD * inline [

Employee|ID|Salary

Bill|001|20000

John|002|30000

Steve|003|35000

] (delimiter is '|');

Citizens:

Load * inline [

Name|Address

Bill|New York

Mary|London

Steve|Chicago

Lucy|Paris

John|Miami

] (delimiter is '|');

EmployeeAddresses:

Load

Name as Employee,

Address

Resident Citizens

where Not Exists (Employee, Name);

Drop Tables Employees, Citizens;

Regards!

Rahul

View solution in original post

7 Replies
rahulpawarb
Specialist III
Specialist III

Please try below:

Employees:

LOAD * inline [

Employee|ID|Salary

Bill|001|20000

John|002|30000

Steve|003|35000

] (delimiter is '|');

Citizens:

Load * inline [

Name|Address

Bill|New York

Mary|London

Steve|Chicago

Lucy|Paris

John|Miami

] (delimiter is '|');

EmployeeAddresses:

Load

Name as Employee,

Address

Resident Citizens

where Not Exists (Employee, Name);

Drop Tables Employees, Citizens;

Regards!

Rahul

Not applicable
Author

thank you very much

BUT it doesn't give the right result what I want.

sunny_talwar

May be like this

TABLE1:
NoConcatenate LOAD
    Date,
    FileName,
    ExcutedDate,

    FileName&Num(ExcutedDate) as Key
FROM (qvd);

TABLE2:
NoConcatenate LOAD
    Date,
    FileName,
    ExcutedDate
FROM (qvd)
WHERE NOT Exists(Key, FileName&Num(ExcutedDate));

rahulpawarb
Specialist III
Specialist III

Could you please share the application file with sample data?

Regards!

Rahul

Not applicable
Author

hi

thank u for asking sample.

here is my sample data.

TABLE1:
NoConcatenate LOAD * inline [
        Date,FileName,ExcutedDate
        20161213,20170420,20170421
        20161214,20170422,20170424
];

TABLE2:
NoConcatenate LOAD * inline [
        Date,FileName,ExcutedDate
        20161210,20170420,20170421
        20161211,20170422,20170424
        20161215,20170424,20170426
]

where ...

the expecting result I want is 2 rows in TABLE1 and the last  row in TABLE2.

maybe in SQL it'll be written like below

(where not (TABLE1.FileName = TABLE2.FileName AND TABLE1.ExcutedDate = TABLE2.ExcutedDate))

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

expecting result:

Date,FileName,ExcutedDate

20161213,20170420,20170421

20161214,20170422,20170424

20161215,20170424,20170426

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

Not applicable
Author

thank you very much .

it works well . but this solution may low the performance when more colums are asked to add to the condition.

i'm looking for a way what "LIKE SQL".

Anyway thank you so much!

Not applicable
Author

hi

i resolved the problem with the code below -maybe it shouldn't be called "problem".

"not (exists(AAA) and exists(BBB))"

thank you very much.