topic Data modelling in QlikView

Data modelling

deepakqlikview_123 — Thu, 20 Mar 2014 06:48:53 GMT

Hello Expert,

Can you please tell me how below code work.

OutletMaster:
load
DealerID as OutletDealerID
resident OrganisationMaster;

left join(OutletMaster)
LOAD
DealerID as OutletDealerID,
OutletID,
OutletCode,

// IsDeleted,
// CreatedBy,

capitalize(OutletName) as [Outlet Name]
FROM $(QvdPath)\VESPA_PRE_OutletMaster.qvd (qvd);

drop Field OutletDealerID;

Moreover can you please suggest when to comment out thefields in script and is ther any chance of data loss when we are commentting fields.

Thanks

Re: Data modelling

amit_saini — Thu, 20 Mar 2014 07:00:31 GMT

Hi Deepak,

We do commenting for following reasons:

For removing Synthetic Keys.(http://community.qlik.com/blogs/qlikviewdesignblog/2013/04/16/synthetic-keys)
For avoiding circular loops.(http://community.qlik.com/blogs/qlikviewdesignblog/2013/06/25/circular-references)

Thanks,

Re: Data modelling

er_mohit — Thu, 20 Mar 2014 07:15:32 GMT

No the data is not loss when we comment the fields the purpose is to commenting is just to remove unwanted fields and for memory utilization and sometimes to remove synthetic keys and to avoid circular loop

like i have two tables

table1:

load Productid,Orderid,Qty,Productname

from table1;

table2:

load Productid,Orderid,Productname,orderdate

from table 2;

when i reload it creating a synthetic key the purpose here to remove synthetic key for this

i load all data from table 1 and also add field (Productid &'|'& Orderid) as Link

and in table 2 i also add field (Productid &'|'& Orderid) as Link

and comment Productid,Orderid and rest of field i fetch

so doing this after reload my table would be link with each other using a field Link

similarly when three or more tables are linked to each pther then create a circular loop to avoid it using alias menas change the name of link or you can make a composite field

however if your data in two tables become unique with several fields the use autonumberhash256() function see it on help side it compact the memory and generate a unique no.

Re: Data modelling

maxgro — Thu, 20 Mar 2014 07:16:32 GMT

// read DealerID from an already in memory table (resident)

OutletMaster:
load
DealerID as OutletDealerID
resident OrganisationMaster;

// join OutletMaster with VESPA_PRE_OutletMaster (qvd file) using DealerID

left join(OutletMaster)
LOAD

// rename field with as.... because Qlik joins by field name
DealerID as OutletDealerID,
OutletID,
OutletCode,

// IsDeleted,
// CreatedBy,

capitalize(OutletName) as [Outlet Name]
FROM $(QvdPath)\VESPA_PRE_OutletMaster.qvd (qvd);

// here you have 1 table, OutletMaster

// drop field used for joining OutletMaster with VESPA_PRE_OutletMaster

drop Field OutletDealerID;

About comments

// comment a line

comment

more lines

no loss of data