Qlik Community

QlikView Deployment

Discussion Board for collaboration related to QlikView Deployment.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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
Valued Contributor II

Re: where condition with other table name in LOAD statment

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

7 Replies
rahulpawarb
Valued Contributor II

Re: where condition with other table name in LOAD statment

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

Re: where condition with other table name in LOAD statment

thank you very much

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

Highlighted

Re: where condition with other table name in LOAD statment

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
Valued Contributor II

Re: where condition with other table name in LOAD statment

Could you please share the application file with sample data?

Regards!

Rahul

Not applicable

Re: where condition with other table name in LOAD statment

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

Re: where condition with other table name in LOAD statment

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

Re: where condition with other table name in LOAD statment

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.

Community Browser