Join causing Table not found error on load.

[#1]

Hi...

I am trying to make a join between the MRC_table(loaded from database) and the TopOffers table(loaded from excel). But, whenever I put the join it gives me some error as Table not found. Dont know where I am going wrong. I am trying to use the script to split up a string in a row into multiple rows and then sum up the values of similar rows into one.

I have attached the qlikview file for reference. The report should display data only for the dates available in excel file. So, I need to join the MRC & TopOffers tables. But, due to the error, I am not able to put the join and the dates for which data is not available also get displayed.

Can someone help me out or give me some hints on how to use joins during loads? I would like to know where i am going wrong as I thought that a join could be made even with a single column match but I dont think thats the case in qlikview.

Thank you.

Regards

Kumar

[john_duffy]

Hi Kumar.

Your table TempTextFields is build from resident table TopOffers and contains the same field names as those in TopOffers. Therefore, QlikView does not actually create a new file called TempTextFields. You can rename the fields when building TempTextFields or add a dummy field to the TempTextFields table to differentiate it from TopOffers.

Let me know if this resolves your issue.

John.

[#3]

Hi John,

Thanks for the reply...

I tried modifying the code in temp text fields and applied the inner join on the Top Offers Table as per the script below:

MRC_TABLE:
Load
 right(MRC,4) as MRC,
 NAME,
 WEEK,
 date(floor(WEEK_START_DATE)) as WEEK_START_DATE,
 date(floor(WEEK_END_DATE)) as WEEK_END_DATE,
 PROVIDER,
 CAT_PROVIDER,
 ORDER_COUNT,
 REVENUE;
SQL select mrc,name,to_char(order_date,'IW')-34 Week,TRUNC(order_date, 'IW') Week_Start_Date,
NEXT_DAY(TRUNC(order_date,'IW'),'SUNDAY') WEEK_END_DATE, provider, cat_provider,count(order_count) order_count,sum(revenue) revenue
from
(select r.mrc,a.name,trunc(r.CREATE_DATE) order_date,count(r.ORDER_OID) order_count,qscp.SVC_PROV provider,qscp.SVC_CAT_PROV cat_provider,sum(r.INV_REVENUE) revenue
from revenue_tab r,qc_report_scp_master qscp,(select NAME,'wfad'||oid mrc from advertisement) a
where r.SCP_OID = qscp.OID
and a.mrc = r.MRC
and a.mrc in(select 'wfad'||oid_advertisement from advertisement_zone a where a.OID_ZONE=2 )
and trunc(r.CREATE_DATE) between to_date('08/30/2010','MM/DD/YYYY') and trunc(sysdate)
group by r.mrc,a.name,trunc(r.CREATE_DATE),qscp.SVC_PROV,qscp.SVC_CAT_PROV)
group by mrc,name,to_char(order_date,'IW'),TRUNC(order_date, 'IW'),NEXT_DAY(TRUNC(order_date,'IW'),'SUNDAY'), provider,cat_provider
order by mrc,to_char(order_date,'IW'),provider,cat_provider;

TopOffers:
INNER JOIN (MRC_TABLE)
LOAD Rowno() as Type,
 date(date#(Date,'MMM DD, YYYY'),'MM/DD/YYYY') as WEEK_START_DATE,
 [On-Site Ads] as Ads,
 Instances as IMPRESSIONS,
 [Ad Clicks] as CLICKS
FROM

(ooxml, embedded labels, header is 2 lines, table is Sheet1);



TempMaxType:
LOAD
 Max(Type) as MaxType //Find how many rows are in the field "Ads"
RESIDENT TopOffers;


Let vType = Peek('MaxType', 0, 'TempMaxType');


For vIT = 1 to vType


TempTextFields:
LOAD
 WEEK_START_DATE as WDATE,
 Ads as Adnames,
 IMPRESSIONS as Imps,
 CLICKS as Clks
RESIDENT TopOffers
Where Type = $(vIT) //Read line by line
;



TempHowMany:
LOAD
 Max(SubStringCount(Adnames, ';;;;')) as HowMany
Resident TempTextFields;

Let vHowMany = Peek('HowMany', 0, 'TempHowMany'); //How many segments are in this line

Drop Table TempHowMany; //*** Needed to have a new table next round

//Split lines into single segments

For vI=1 to $(vHowMany)+1

Let vIName = vI-1;

Temp_Fields:
JOIN (TopOffers)
Load
 WDATE as WEEK_START_DATE,
 Adnames ,
 Imps,
 Clks,
 right(SubField(Adnames, ';;;;', $(vI)),4) as MRC
Resident TempTextFields;


Next

Drop Table TempTextFields; //*** Needed to have a new table next round

Next

But, the error this time around was the same "table not found" for table top offers when i tried to select the max(type) from topoffers in the load TempMaxType.

I am almost there with the code except the join part.

Thanks for your help.

Regards

Kumar

[john_duffy · Accepted solution]

Hi Kumar.

Since you inner joined TopOffers to the MRC_Table the tabe TopOffers is not actually created. To have that table available in the script change the INNER JOIN to an INNER KEEP.

[#5]

Hi John,

Thanks a lot...u really saved a lot of time for me...

I wasnt aware of the keep syntax...

the script worked like a charm...

the final script is as below:

we need not modify the column names for the top offers...the keep part works fine...

MRC_TABLE:
Load
 right(MRC,4) as MRC,
 NAME,
 WEEK,
 date(floor(WEEK_START_DATE)) as WEEK_START_DATE,
 date(floor(WEEK_END_DATE)) as WEEK_END_DATE,
 PROVIDER,
 CAT_PROVIDER,
 ORDER_COUNT,
 REVENUE;
SQL select mrc,name,to_char(order_date,'IW')-34 Week,TRUNC(order_date, 'IW') Week_Start_Date,
NEXT_DAY(TRUNC(order_date,'IW'),'SUNDAY') WEEK_END_DATE, provider, cat_provider,count(order_count) order_count,sum(revenue) revenue
from
(select r.mrc,a.name,trunc(r.CREATE_DATE) order_date,count(r.ORDER_OID) order_count,qscp.SVC_PROV provider,qscp.SVC_CAT_PROV cat_provider,sum(r.INV_REVENUE) revenue
from revenue_tab r,qc_report_scp_master qscp,(select NAME,'wfad'||oid mrc from advertisement) a
where r.SCP_OID = qscp.OID
and a.mrc = r.MRC
and a.mrc in(select 'wfad'||oid_advertisement from advertisement_zone a where a.OID_ZONE=2 )
and trunc(r.CREATE_DATE) between to_date('08/30/2010','MM/DD/YYYY') and trunc(sysdate)
group by r.mrc,a.name,trunc(r.CREATE_DATE),qscp.SVC_PROV,qscp.SVC_CAT_PROV)
group by mrc,name,to_char(order_date,'IW'),TRUNC(order_date, 'IW'),NEXT_DAY(TRUNC(order_date,'IW'),'SUNDAY'), provider,cat_provider
order by mrc,to_char(order_date,'IW'),provider,cat_provider;

TopOffers:
INNER KEEP (MRC_TABLE)
LOAD Rowno() as Type,
 date(date#(Date,'MMM DD, YYYY'),'MM/DD/YYYY') as WEEK_START_DATE,
 [On-Site Ads] as Ads,
 Instances as IMPRESSIONS,
 [Ad Clicks] as CLICKS
FROM

(ooxml, embedded labels, header is 2 lines, table is Sheet1);



TempMaxType:
LOAD
 Max(Type) as MaxType //Find how many rows are in the field "Ads"
RESIDENT TopOffers;


Let vType = Peek('MaxType', 0, 'TempMaxType');


For vIT = 1 to vType


TempTextFields:
LOAD
 WEEK_START_DATE ,
 Ads ,
 IMPRESSIONS,
 CLICKS
RESIDENT TopOffers
Where Type = $(vIT) //Read line by line
;



TempHowMany:
LOAD
 Max(SubStringCount(Ads, ';;;;')) as HowMany
Resident TempTextFields;

Let vHowMany = Peek('HowMany', 0, 'TempHowMany'); //How many segments are in this line

Drop Table TempHowMany; //*** Needed to have a new table next round

//Split lines into single segments

For vI=1 to $(vHowMany)+1

Let vIName = vI-1;

Temp_Fields:
JOIN (TopOffers)
Load
 WEEK_START_DATE,
 Ads ,
 IMPRESSIONS,
 if($(vHowMany)=0,CLICKS,0) as CLICKS,
 right(SubField(Ads, ';;;;', $(vI)),4) as MRC
Resident TempTextFields;


Next

Drop Table TempTextFields; //*** Needed to have a new table next round

Next