Data in Fields Droped with Source table

[#1]

Hi.

I use following schema to load Fact and linked table in my qlikview files

1 Load data to source table

2 reload it to fact

3 reload to link table

4 drop source table

<code>

src_PromptAntagCalciumProduct:
LOAD
AutoNumber('PRM_ANT_CALC_PROD' & '+' & ank_num & '+' & ProductID , 'CommonLinkPROD') as %key_prod,
ank_num,
NumOfPatAC_AG,
NumOfPatAC_IBS,
NumOfPatAC_AG_IBS,
ProductID;
SQL SELECT "ank_num", "NumOfPatAC_AG", "NumOfPatAC_IBS", "NumOfPatAC_AG_IBS",
OrderNum, ProductID
FROM dwh.PromptAntagCalciumProduct;



PromptAntagCalciumProduct:
LOAD
"%key_prod",
NumOfPatAC_AG,
NumOfPatAC_IBS,
NumOfPatAC_AG_IBS,
1 as PromptAntagCalciumProductCnt
resident src_PromptAntagCalciumProduct;

CommonLinkPROD:
LOAD
"%key_prod",
ank_num,
ProductID
resident src_PromptAntagCalciumProduct;

DROP TABLE src_PromptAntagCalciumProduct;

</code>

But sometimes I meet situation when data from fact and link table droped with src table.

Some manipulations with field names helping avoid this situation.

Can anyboady explain how create code which drop only source table and not affect others.

[gandalfgray]

A mistake you can easily do as you start writing load scripts in qlikview is to write something like:

Table2:
Load *
Resident Table1;

In this case you will not get a Table2, since they have exactly the same fields.

As you noticed, manipulating the fields so they differ will solve this problem.

[chris_johnson]

Hi Vladimir,

It looks like you have the same field names in the tables 'src_PromptAntagCalciumProduct' and 'CommonLinkPROD'. I'm no expert (!) but it may be that QlikView is not creating the table CommonLinkPROD and instead putting the data into src_PromptAntagCalciumProduct.

QlikView will ignore creating tables when you are trying to create them and there is another table with a similar structure. For example: Table1:
LOAD
a,
b,
c
resident MyTable;

Table2:
LOAD
a,
b
resident MyTable;

I believe in this case, and hopefully someone with more knowledge can say if I'm right or not, Table2 will not be created because the fields can be found in Table1. QlikView will load the data into Table1.

This could be happening in your case. You could try commenting out the DROP TABLE statement to see if the CommonLinkPROD table is created. If it isn't then it is doing what I have described above.

If it is doing this then the easiest thing to do is change the script to say: CommonLinkPROD: NOCONCATENATE LOAD "%key_prod"...

Using the NOCONCATENATE keyword stops QlikView from automatically concatenating the tables together.

Hope this helps,

Chris

[#4]

Additional workaround :

Example of statements which produce dropping related data:

src_PromoVisitOftenCompany:
LOAD
AutoNumber('PROM_VISIT_OFTEN' & '+' & ank_num & '+' & CompanyID , 'CommonLinkCOMPANY') as "%key_company",
ank_num,
CompanyID ;
SQL SELECT ank_num, CompanyID, OrderNum
FROM dwh.PromoVisitOftenCompany;


PromoVisitOftenCompany:
LOAD
"%key_company",
1 as PromVisitOftenCnt
resident src_PromoVisitOftenCompany;

CommonLinkCOMPANY:
LOAD
"%key_company",
ank_num,
CompanyID
resident src_PromoVisitOftenCompany;

DROP TABLE src_PromoVisitOftenCompany;

But when I change source table like this

src_PromoVisitOftenCompany:
LOAD
AutoNumber('PROM_VISIT_OFTEN' & '+' & ank_num & '+' & CompanyID , 'CommonLinkCOMPANY') as "%key_company",
ank_num,
CompanyID,

1 as SomeAdditionalField

;
SQL SELECT ank_num, CompanyID, OrderNum
FROM dwh.PromoVisitOftenCompany;

data remain in CommonLinkCOMPANY

Any suggestions??

[#5 · Accepted solution]

try NOCONCATENATE LOAD instead of just LOAD. This will make sure Qlikview loads a separate table even if the field names match

[#6]

NOCONCATENATE statement solve my problem. Thanks so much

[#7]

NOCONCATENATE works only for first statment.

In my file data loaded from several sources:

I add dummy field which not appear in other tables.. this solve a problem for all other blocks.

Here is example from three blocks:

/**********************************************************************************************/
/*** q14 ***/
src_PromoVisitOftenCompany:
LOAD
AutoNumber('PROM_VISIT_OFTEN' & '+' & ank_num & '+' & CompanyID , 'CommonLinkCOMPANY') as "%key_company",
ank_num, CompanyID, OrderNum, 0 as DummyField;
SQL SELECT ank_num, CompanyID, OrderNum
FROM dwh.PromoVisitOftenCompany;


PromoVisitOftenCompany:
LOAD
"%key_company",
1 as PromVisitOftenCnt
resident src_PromoVisitOftenCompany;

CommonLinkCOMPANY:
LOAD
"%key_company",
ank_num,
CompanyID
resident src_PromoVisitOftenCompany;

DROP TABLE src_PromoVisitOftenCompany;


/**********************************************************************************************/
/*** q15 ***/

src_PromoMostProfCompany:
LOAD
AutoNumber('PROM_MOST_PROF' & '+' & ank_num & '+' & CompanyID , 'CommonLinkCOMPANY') as "%key_company",
ank_num, CompanyID, OrderNum, 0 as DummyField;
SQL SELECT ank_num, CompanyID, OrderNum
FROM dwh.PromoMostProfessionalCompany;

PromoMostProfessionalCompany:
LOAD
"%key_company",
1 as PromoMostProfCnt
resident src_PromoMostProfCompany;

CommonLinkCOMPANY:
LOAD
"%key_company",
ank_num,
CompanyID
resident src_PromoMostProfCompany;

DROP TABLE src_PromoMostProfCompany;


/**********************************************************************************************/
/*** q16 ***/

src_PromoLastWeekCompany:
LOAD
AutoNumber('PROM_LAST_WEEK' & '+' & ank_num & '+' & CompanyID , 'CommonLinkCOMPANY') as "%key_company",
ank_num, CompanyID, 0 as DummyField;
SQL SELECT ank_num, CompanyID, OrderNum
FROM dwh.PromoLastWeekCompany;


PromoLastWeekCompany:
LOAD
"%key_company",
1 as PromoLastWeekCnt
resident src_PromoLastWeekCompany;

CommonLinkCOMPANY:
LOAD
"%key_company",
ank_num,
CompanyID
resident src_PromoLastWeekCompany;

DROP TABLE src_PromoLastWeekCompany;

[chris_johnson]

Hi Vladimir,

I think the same problem that I have described earlier is happening on all 3 blocks. Like you say, I guess you can use NOCONCATENATE LOAD for the first load statement in each block.

How about if you store the CommonLinkCOMPANY tables in each of your block to different tables (perhaps CommonLinkCOMPANY_1, CommonLinkCOMPANY_2...) and then concatenate load them together once you have finished loading into the tables and drop the individual tables?

I'm sure there is probably a better way to do this though!

Chris

[#9]

Problem appear when source table has the same structure as LinkTable or some table in chain 1,2,3,4.

Like for me loading data to link table directly in each block minimize code.

I prefer use this structure.