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

Getting Error while loading data

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;

9 Replies
jyothish8807
Master II
Master II

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

Best Regards,
KC
Gabriel
Partner - Specialist III
Partner - Specialist III

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

Not applicable
Author

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
Partner - Specialist III
Partner - Specialist III

Can you attach a sample file?

It's easier that way

Not applicable
Author

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

Not applicable
Author

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?