Getting Error while loading data

[#1]

Hi All,

I am trying to reload below code up to last join in 2nd tab reloading data smoothly. But when it comes to last left join in Tab2 , system is hanging up to 30 min then getting error as general script error. Even if i change the order of last 4 tables i am facing same issue (Hanging on 4th table). Could you please help me here.

Tab1:-

Act:

Load

ActID,

Begdate,

category

ICID,

Amount

from Act.Qvd(qvd);

left join

Load

Campkey,

CampID as ActID

from camp.qvd(qvd);

left join

Load

ActID,

Amt1,

Amt2,

Amt3,

Amt4

from resp.qvd(qvd);

left Join

Load

Lkey,

CampKey

from LCamp.qvd(qvd);

left join

Load

LKey,

LID

from LData.qvd(qvd);

Tab2:-

Empty:

Load

ActID,

Sum(Amt1)+Sum(Amt2)+Sum(Amt3)+Sum(Amt4)+Count(LID) as Resp

resident Act group by ActID;

Left Join

Load

Lkey,

CampKey

from LCamp.qvd(qvd) where exists(CampKey) and Flag='Y';

Left Join

Load

Lkey,

Flag,

LID

from LData.qvd(qvd) where exists(LKey) and Converted='0';

Left Join

Load

CampKey,

CampIndicator,

Okey

from OCamp.qvd(qvd) where exists(CampKey) and CampIndicator='Y';

Left join

Load

OID,

ConvfromLD,

Okey

from OData.qvd(Qvd) where exists(Okey);

Final:

Noconcatenate

load

ActID,

if(Sum(Resp)+count(if(Flag='Y',LID))+Count(if(CampIndicator='Y',OID))=0,'Yes','No') as DispFlag

resident Empty group by ActID;

drop table Empty;

[jyothish8807]

Hi Reddy,

I believe this is because you are joining the same table again in Tab2, when the data is already present in the table Act.

Try to store ACT table into a QVD. Then comment the script  of ACT Table and use the QVD in Tab2.

Regards

KC

[Gabriel]

Hi,

The tab 2 that you're left joining don't have common field. See my comments

Tab2:-

Empty:

Load

ActID,

Sum(Amt1)+Sum(Amt2)+Sum(Amt3)+Sum(Amt4)+Count(LID) as Resp

resident Act group by ActID;

Left Join

Load

ActID  //This field has to be included in here

Lkey,

CampKey

from LCamp.qvd(qvd) where exists(CampKey) and Flag='Y';

Left Join

Load

Lkey,

Flag,

LID

from LData.qvd(qvd) where exists(LKey) and Converted='0';

Left Join

Load

Lkey //Same here,

CampKey,

CampIndicator,

Okey

from OCamp.qvd(qvd) where exists(CampKey) and CampIndicator='Y';

Left join

Load

Lkey //Same here,

OID,

ConvfromLD,

Okey

from OData.qvd(Qvd) where exists(Okey);

Final:

Noconcatenate

load

ActID,

if(Sum(Resp)+count(if(Flag='Y',LID))+Count(if(CampIndicator='Y',OID))=0,'Yes','No') as DispFlag

resident Empty group by ActID;

drop table Empty;

[sunny_talwar]

Instead of just dropping Empty, try to dropping both Empty and Act.

In addition to this I see that on your tab2 you are doing some left joins into Empty but those won't do anything because you don't have any matches with the actually Empty:

Tab2:-

Empty:

Load

ActID,

Sum(Amt1)+Sum(Amt2)+Sum(Amt3)+Sum(Amt4)+Count(LID) as Resp

resident Act group by ActID;

Left Join

Load

Lkey, //Not Present above in Empty

CampKey //Not Present above in Empty

from LCamp.qvd(qvd) where exists(CampKey) and Flag='Y';

Similarly, none of the other joins will actually do any join. You will only have the intial Empty table which is using a resident load from Act.

HTH

Best,

Sunny

[#5]

Hi Gabriel,

yes there is a common field between first two tables that column is "Campkey". I forgot to add that column. But it is there in my code.

Could you please Help me here.

Thanks in Advance,

Reddy

[Gabriel]

Can you attach a sample file?

It's easier that way

[#7]

Hi Sunny,

Thanks for your valuable suggestion.

But i have common keys between first two tables. by mistake i didn't type that column.

is there any alternate solution for this? Because i need Act and Final tables should exist in data model.

Thanks,

Reddy

[sunny_talwar]

Alternate is to use Qualify statement for one of the two table leaving those fields which you need to connect

Tab1

As it is

Tab2 //Before your final table, add this:

QUALIFY *;

UNQUALIFY ActID; //ActID is just an example, you might need to UNQUALIFY more fields based on your requirement

Final:

Noconcatenate

load

ActID,

if(Sum(Resp)+count(if(Flag='Y',LID))+Count(if(CampIndicator='Y',OID))=0,'Yes','No') as DispFlag

resident Empty group by ActID;

UNQUALIFY *;

What Qualify does is changes the name of the field from FieldName to Final.FieldName which helps against not required synthetic keys

HTH

Best,

Sunny

[#9]

Hi Sunny,

Thanks for your reply.

Even with this code also i am facing same issue :s

[sunny_talwar]

Same problem of where it takes 30 mins or so to finally complete the reload???

Would you be able to share your complete script?