where condition with other table name in LOAD statment

[#1]

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;

[rahulpawarb · Accepted solution]

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

[#3]

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]

Could you please share the application file with sample data?

Regards!

Rahul

[#6]

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

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

[#7]

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!

[#8]

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.