Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

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

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

Re: Getting Error while loading data

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

Re: Getting Error while loading data

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;

Re: Getting Error while loading data

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

Re: Getting Error while loading data

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

Re: Getting Error while loading data

Can you attach a sample file?

It's easier that way

Not applicable

Re: Getting Error while loading data

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

Re: Getting Error while loading data

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

Re: Getting Error while loading data

Hi Sunny,

Thanks for your reply.

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

Re: Getting Error while loading data

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

Would you be able to share your complete script?

Community Browser