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

[jagan]

Attach some rows from trans_detail table also.

[arulsettu]

is this ok

[jagan]

Hi,

Column names for Transaction table are missing, update it.

Regards,

Jagan.

[arulsettu]

please check this

[jagan · Accepted solution]

Hi,

Try like this

Account:

LOAD ACNT_COMP_CODE & ACNT_CODE as %key,

   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 account.xls

(biff, embedded labels, table is [Sheet 1$]);

Concatenate(Account)

LOAD

ACNT_COMP_CODE & ACNT_FLEX_06 as %key,

  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 account.xls

(biff, embedded labels, table is [Sheet 1$]);

Budget:

LOAD

*,

Year(TD_DOC_DT_date) AS Year,

Month(TD_DOC_DT_date) AS Month;

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

[budget_ins.xls]

(biff, embedded labels, table is [Sheet 1$]);

Trans:

LOAD

*,

Year(TD_DOC_DT_date) AS Year,

Month(TD_DOC_DT_date) AS Month;

LOAD TD_COMP_CODE & TD_MAIN_ACNT_CODE as %key,

  TD_COMP_CODE,   

  date(TD_DOC_DT) as TD_DOC_DT_date,   

     TD_MAIN_ACNT_CODE,

     TD_DRCR_FLAG,

     TD_AMT_LC_1

     FROM

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

(qvd);

Regards,

Jagan.

[arulsettu]

thank you very much jagan one doubt month and year creating syntesis key how to avoid it please

thanks

[jagan]

Concatenate Budget and Trans table

Data:

LOAD

*,

Year(TD_DOC_DT_date) AS Year,

Month(TD_DOC_DT_date) AS Month;

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

[budget_ins.xls]

(biff, embedded labels, table is [Sheet 1$]);

Concatenate(Data)

LOAD

*,

Year(TD_DOC_DT_date) AS Year,

Month(TD_DOC_DT_date) AS Month;

LOAD TD_COMP_CODE & TD_MAIN_ACNT_CODE as %key,

  TD_COMP_CODE,  

  date(TD_DOC_DT) as TD_DOC_DT_date,  

     TD_MAIN_ACNT_CODE,

     TD_DRCR_FLAG,

     TD_AMT_LC_1

     FROM

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

(qvd);

[arulsettu]

Thank you very very much Jagan. your always helping.

thanks  once again