Data Model Issue

Fri, 26 Dec 2014 10:20:53 GMT

Hi All,

While doing discovery on charts ,selections are not happening in following tables Energy sale and Revenue. Here Billing is main table got raw data remaining tables are having calculated fields (info : all are excel tables for POC). Do i need to create a separate Fact table if so help me on this issue.

Thanks.

Re: Data Model Issue

its_anandrjs — Fri, 26 Dec 2014 10:26:01 GMT

Hi,

Use joins on the key fields and create the single table out of different tables on the basis of the key fields or primary keys.

Regards

Anand

Re: Data Model Issue

Fri, 26 Dec 2014 10:30:52 GMT

Can you give me sample with the help of that data .

Thanks Anand.

Re: Data Model Issue

Fri, 26 Dec 2014 10:41:18 GMT

AS Anand said u have to join the tables , those u have in ur script ....

For that u have to do left join or right join based on ur requirement... , if possible post sample script , so that we can help u batter .......

Br,

Venki

Re: Data Model Issue

Fri, 26 Dec 2014 11:13:19 GMT

Billing_Feb_14:

LOAD

Month(Date#('201402','YYYYMM')) as MNTH,

Year(Date#('201402','YYYYMM')) as YEAR,

Month(Date#(BILL_MONTH,'YYYYMM')) AS MONTH,

ZONE,

BU,

CONNECTION_NO,

PREV_SERVNO,

DIVISION,

SUB_DIVISION,

BOOKNO,

AREA_CODE,

TARIFF_CATEGORY as CATEGORY,

TARIFF_CODE,

NAME,

ADDRESS1,

ADDRESS2,

ADDRESS3,

SUPPLY_VOL ,

CONTRACTED_DEMAND1,

CONTRACTED_DEMAND2,

BILL_DEMAND1,

BILL_DEMAND2,

CONNECTED_LOAD,

CONNECTED_LOAD_UNIT,

FROM_READDT,

TO_READDT,

NO_OF_DAYS_OF_BILL,

MF_1,

nWH_INITIAL_READ_1,

nWH_CLOSING_READ_1,

nWH_CONSMP_1,

nVAH_INITIAL_READ_1,

mVAH_CLOSING_READ_1,

nVAH_CONSMP_1,

MF_2,

mWH_INITIAL_READ_2,

mWH_CLOSING_READ_2,

mWH_CONSMP_2,

mVAH_INITIAL_READ_2,

mVAH_CLOSING_READ_2,

mVAH_CONSMP_2,

PF_1,

PF_2,

MAX_DEMAND_1,

MAX_DEMAND_2,

FIXED_CHARGES,

_CHARGES,

DIFF_MIN_CHARGES,

EXCESS_MD_CHARGES,

_DUTY,

OTHER_CHARGES_AC,

TOD_CHARGE,

............................................

................................................

CR_EC,

CR_DIFF_MINCHG,

CR_OTHER_CHARGES,

CR_GOVT_TAX,

CR_TOTAL_CHARGES

FROM

(ooxml, embedded labels, table is [Billing & Collection Data]);

ATR_Cal_Feb_14:

LOAD F1,

Gross,

[Ass. Adj],

Dr,

Cr.,

Net,

F7,

Category,

[Net Units],

[Net FC],

[Net EC],

[Net Diff Min Chg],

Total,

ATR,

FROM

);

ATC_Losses_Feb14:

LOAD Month(Date(Month)) AS MONTH,

[ Input (MU)],

[ Billed Excl. DOE (MU)],

[Total Loss (MU)],

[Distribution Loss %],

[Total Revenue Billed (excl. DOE) (Rs. Lac)],

[Total Collected Amount (excl. DOE) (Rs. Lac)],

[Collection Efficiency %],

[AT & C Loss (MU)],

[AT &C Loss (%)],

FROM

);

Revenue_Feb14:

LOAD PARTICULARS as Category,

[Fixed Charges (Rs. Lac)],

[Energy Charges (Rs. Lac)],

[Minimum Charges (Rs. Lac)],

[Excess MD Charges (Rs. Lac)],

[ Duty (Rs. Lac)],

[Other Charges (Rs. Lac)],

[Total Current Billed Amount (Rs. Lac)],

[Amount collected during the month (Rs. Lac)],

[Upto date TPL Arrears (Rs.)],

[Upto date DVVNL Arrears (Rs.)],

[Total Arrears ( Rs. Lac)]

FROM

);

EnergySales_Feb14:

LOAD [Sl No],

[Code No] as CATEGORY,

Category,

[No of Consumers billed during the month],

[Sanctioned Load (KW) at the end of the month],

[Sales (MUs) during the month*],

FROM

);

like this i got two more months data DEC and JAN .i loaded these table in same way in other tabs ,Concatenation done by Qlikview.

is this info help full Venki,

Thanks

topic Re: Data Model Issue in QlikView

Data Model Issue

Re: Data Model Issue

Re: Data Model Issue

Re: Data Model Issue

Re: Data Model Issue