topic Re: Getting Error while loading data in QlikView

Getting Error while loading data

Wed, 03 Jun 2015 11:45:08 GMT

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,

Re: Getting Error while loading data

jyothish8807 — Wed, 03 Jun 2015 11:55:09 GMT

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

Re: Getting Error while loading data

Gabriel — Wed, 03 Jun 2015 12:00:33 GMT

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

sunny_talwar — Wed, 03 Jun 2015 12:02:17 GMT

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

Re: Getting Error while loading data

Wed, 03 Jun 2015 12:31:10 GMT

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

Re: Getting Error while loading data

Gabriel — Wed, 03 Jun 2015 12:33:04 GMT

Can you attach a sample file?

It's easier that way

Re: Getting Error while loading data

Wed, 03 Jun 2015 12:39:34 GMT

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

sunny_talwar — Wed, 03 Jun 2015 12:46:39 GMT

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

Re: Getting Error while loading data

Wed, 03 Jun 2015 12:55:53 GMT

Hi Sunny,

Thanks for your reply.

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

Re: Getting Error while loading data

sunny_talwar — Wed, 03 Jun 2015 13:00:30 GMT

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

Would you be able to share your complete script?