Data Model Issue

[#1]

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.

[its_anandrjs]

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

[#3]

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

Thanks Anand.

[#4]

Hi

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

[#5]

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,

     O,

     P,

     Q

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 (%)],

     K,

     L,

     M

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*],

     G,

     H,

     I

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