Skip to main content
Announcements
Marching toward a simplified navigation! READ ON
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How do i refer to the field from previosly loaded qvd

This is how i am loading data from my first QVD:

QUALIFY *;

UNQUALIFY Id;

PreviousMonthData:

LOAD

     Id,

     Name,

     Price AS PreviousMonthPrice,

     MetaData1 AS PreviousMonthMetaData1,

    MetaData2 AS PreviousMonthMetaData2

FROM

[QVDs\Mar-2015.qvd]

(qvd);

UNQUALIFY *;

This is my second qvd.

CurrentMonthData:

LEFT JOIN (PreviousMonthData)

LOAD

     Id,

     Name,

     Price AS CurrentMonthPrice,

     LastPriceChangedON

     MetaData1 AS CurrentMonthMetaData1,

    MetaData2 AS CurrentMonthMetaData2

FROM

[QVDs\Apr-2015.qvd]

(qvd);

What i am trying to do is have a field in CurrentMonthData by comparing PreviousMonth's MetaData1 to Current Month's MetaData1 and PreviousMonth's MetaData2 to Current Month's MetaData2 and if they do not match, set that fields value to 'Changed'.

This is what i have tried so far:

CurrentMonthData:

LEFT JOIN (PreviousMonthData)

LOAD

     Id,

     Name,

     Price AS CurrentMonthPrice,

     LastPriceChangedON

     MetaData1 AS CurrentMonthMetaData1,

    MetaData2 AS CurrentMonthMetaData2,

    IF (PreviousMonthData.PreviousMonthMetaData1 <> MetaData1 OR PreviousMonthData.PreviousMonthMetaData2 <> MetaData2,' Changed', 'Not Changed')

FROM

[QVDs\Apr-2015.qvd]

(qvd);

The above Load gives me an error saying it cannot find PreviousMonthData.PreviousMonthMetaData1.

Is there a way i can refer to PreviousMonthData without using Resident Table?

1 Solution

Accepted Solutions
sunny_talwar

I am not sure why you need Qualify and Unqualify statements here, but the basic idea is to do something like this:

PreviousMonthData:

LOAD

    Id,

    Name,

    Price AS PreviousMonthPrice,

    MetaData1 AS PreviousMonthMetaData1,

    MetaData2 AS PreviousMonthMetaData2

FROM

[QVDs\Mar-2015.qvd]

(qvd);

LEFT JOIN (PreviousMonthData) //This join will be on Id and Name, if you don't want this then you can QUALIFY Name statement and the join will be just on Id.

LOAD

    Id,

    Name,

    Price AS CurrentMonthPrice,

    LastPriceChangedON

    MetaData1 AS CurrentMonthMetaData1,

    MetaData2 AS CurrentMonthMetaData2

FROM

[QVDs\Apr-2015.qvd]

(qvd);


Joined Table:

LOAD *,

          IF (PreviousMonthMetaData1 <> CurrentMonthMetaData1 or PreviousMonthMetaData2 <> CurrentMonthMetaData2,' Changed', 'Not Changed') as Status

Resident PreviousMonthData;


DROP Table PreviousMonthData;



HTH

Best,

Sunny

View solution in original post

2 Replies
sunny_talwar

I am not sure why you need Qualify and Unqualify statements here, but the basic idea is to do something like this:

PreviousMonthData:

LOAD

    Id,

    Name,

    Price AS PreviousMonthPrice,

    MetaData1 AS PreviousMonthMetaData1,

    MetaData2 AS PreviousMonthMetaData2

FROM

[QVDs\Mar-2015.qvd]

(qvd);

LEFT JOIN (PreviousMonthData) //This join will be on Id and Name, if you don't want this then you can QUALIFY Name statement and the join will be just on Id.

LOAD

    Id,

    Name,

    Price AS CurrentMonthPrice,

    LastPriceChangedON

    MetaData1 AS CurrentMonthMetaData1,

    MetaData2 AS CurrentMonthMetaData2

FROM

[QVDs\Apr-2015.qvd]

(qvd);


Joined Table:

LOAD *,

          IF (PreviousMonthMetaData1 <> CurrentMonthMetaData1 or PreviousMonthMetaData2 <> CurrentMonthMetaData2,' Changed', 'Not Changed') as Status

Resident PreviousMonthData;


DROP Table PreviousMonthData;



HTH

Best,

Sunny

Not applicable
Author

Just now figured it out.