Common Date

[arulsettu]

hi i am calculating actual and budget amount

both tables having different tables. and i want to make common date for both tables

Trans_detail:

  LOAD *,

  date(TD_DOC_DT) as TD_DOC_DT_date;

LOAD TD_COMP_CODE & TD_MAIN_ACNT_CODE as %key,

  TD_COMP_CODE,

     TD_DOC_DT,

     day(TD_DOC_DT) as TD_day,

     month(TD_DOC_DT) as Td_month,

     Year(TD_DOC_DT) as TD_YEAR,

     Date(MonthStart(TD_DOC_DT),'MMM-YYYY') as yearmonths,

    'Q'& Ceil(Month(TD_DOC_DT)/3) as Td_Quarter,

     TD_MAIN_ACNT_CODE,

     TD_DRCR_FLAG,

     TD_AMT_LC_1

     FROM

"C:\ProgramData\QlikTech\Documents\New folder\QVD\[Trans Detail].qvd"

(qvd);

Budget_ins:

LOAD BI_COMP_CODE & BI_LOB_CODE as %key1,

  BI_COMP_CODE,

     BI_DIVN_CODE,

     BI_LOB_CODE,

     BI_TYPE,

     BI_YEAR ,

     BI_MONTH,

     MakeDate(BI_YEAR,BI_MONTH) as TD_DOC_DT_date,

     BI_VALUE

FROM

(qvd);

Account:

LOAD ACNT_COMP_CODE & ACNT_CODE as %key,

  ACNT_COMP_CODE & ACNT_FLEX_06 as %key1,

  ACNT_COMP_CODE,

  ACNT_CODE,

     ACNT_NAME,

     ACNT_SHORT_NAME,

     ACNT_CATG,

     ACNT_FLAG,

     ACNT_TYPE,

     ACNT_FRZ_FLAG,

     ACNT_CR_UID,

     ACNT_CR_DT,

     ACNT_FLEX_01,

     ACNT_FLEX_02,

     ACNT_FLEX_03,

     ACNT_FLEX_04,

     ACNT_FLEX_05,

     ACNT_FLEX_06,

     ACNT_COMP_NAME,

     ACNT_COMP_SHORT_NAME

FROM

(qvd);

how to make common date for both tables.

plz someone suggest

thanks

[arulsettu]

any help guys its urgent

[arulsettu]

anyone plz suggest

[jjordaan]

Hi Arul,

Concattenate the Trans_Detail and Budget_ins table into one table.

The columns that are the same give them the same column name.

If you want you can create a new column 'Trans' for Trans_Detail and 'budget' for Budget_ins and give it the Identifier.

This you can use in set analysis.

Hope this helps.

[arulsettu]

hi

     its creating synthesis key and no result

thanks

[jagan]

Hi,

Try like this

Trans_detail:

LOAD TD_COMP_CODE & TD_MAIN_ACNT_CODE as %key,

  TD_COMP_CODE,

     TD_DOC_DT,

date(TD_DOC_DT) as TD_DOC_DT_date

     day(TD_DOC_DT) as TD_day,

     month(TD_DOC_DT) as Td_month,

     Year(TD_DOC_DT) as TD_YEAR,

     Date(MonthStart(TD_DOC_DT),'MMM-YYYY') as yearmonths,

    'Q'& Ceil(Month(TD_DOC_DT)/3) as Td_Quarter,

     TD_MAIN_ACNT_CODE,

     TD_DRCR_FLAG,

     TD_AMT_LC_1

     FROM

"C:\ProgramData\QlikTech\Documents\New folder\QVD\[Trans Detail].qvd"

(qvd);

Budget_ins:

LOAD

*,

day(TD_DOC_DT_date) as TD_day,

     month(TD_DOC_DT_date) as Td_month,

     Year(TD_DOC_DT_date) as TD_YEAR,

     Date(MonthStart(TD_DOC_DT_date),'MMM-YYYY') as yearmonths,

    'Q'& Ceil(Month(TD_DOC_DT_date)/3) as Td_Quarter;

LOAD BI_COMP_CODE & BI_LOB_CODE as %key,

  BI_COMP_CODE,

     BI_DIVN_CODE,

     BI_LOB_CODE,

     BI_TYPE,

      MakeDate(BI_YEAR,BI_MONTH) as TD_DOC_DT_date,

     BI_VALUE

FROM

(qvd);

Hope this helps you.

Regards,

Jagan.

[ankitaag]

Hi Arul,

I have the same answer as Jeroen, the best way to have a common date is to concatenate your tables and have an identifier or flag (Trans or Budeget etc..) so that while using the date in your expressions yo can write a condition for flag = Trans/Budget so that you don't get the data twice or thrice.

Thanks and Regards,

Ankita

[arulsettu]

Hi Jagan,

              data model changed budget is connected with account via %key1. if change it wont give result i think. please check the app and help me.

[arulsettu]

thanks Ankita plz check the above app

[SunilChauhan]

Create generic key in new fact table using below

ID&Month as Key

from first table

concatenate

ID&date as key

from second table

link this table to same key to first table and second table

i.e

ID&Month as Key in Budget table

ID&Date as key in actual table

hope this helps