topic Re: Common Date in QlikView

Common Date

arulsettu — Tue, 14 Apr 2015 04:44:31 GMT

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

Re: Common Date

arulsettu — Tue, 14 Apr 2015 04:57:13 GMT

any help guys its urgent

Re: Common Date

arulsettu — Tue, 14 Apr 2015 05:25:53 GMT

anyone plz suggest

Re: Common Date

jjordaan — Tue, 14 Apr 2015 05:26:13 GMT

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.

Re: Common Date

arulsettu — Tue, 14 Apr 2015 05:36:48 GMT

its creating synthesis key and no result

thanks

Re: Common Date

jagan — Tue, 14 Apr 2015 05:40:38 GMT

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.

Re: Common Date

ankitaag — Tue, 14 Apr 2015 05:50:00 GMT

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

Re: Common Date

arulsettu — Tue, 14 Apr 2015 05:50:04 GMT

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.

Re: Common Date

arulsettu — Tue, 14 Apr 2015 05:51:40 GMT

thanks Ankita plz check the above app

Re: Common Date

SunilChauhan — Tue, 14 Apr 2015 06:01:13 GMT

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

Re: Common Date

arulsettu — Tue, 14 Apr 2015 06:06:30 GMT

can you plz chck the app and tel me how to do that

thanks

Re: Common Date

arulsettu — Tue, 14 Apr 2015 06:29:24 GMT

Hi Jagan,

above suggestion is not giving the exact result can you please check the app and tell me what i am doing wrong

thanks

Re: Common Date

arulsettu — Tue, 14 Apr 2015 06:50:40 GMT

any help guys

Re: Common Date

arulsettu — Tue, 14 Apr 2015 07:15:28 GMT

Jagan Can you please help

Re: Common Date

jagan — Tue, 14 Apr 2015 07:21:29 GMT

Hi,

Can you attach some sample data for all the tables?

Regards,
Jagan.

Re: Common Date

arulsettu — Tue, 14 Apr 2015 07:30:59 GMT

data is too large Jagan so i am attaching qvw without reducing data. if it is not enough please let me know

thanks

Re: Common Date

arulsettu — Tue, 14 Apr 2015 07:33:52 GMT

i need budget_ins date and Trans_detail date common plz help

Re: Common Date

jagan — Tue, 14 Apr 2015 07:35:17 GMT

Hi Arul,

I need sample data to create datamodel, I need some hunded of rows from each table to create script.

Regards,

Jagan.

Re: Common Date

arulsettu — Tue, 14 Apr 2015 07:39:41 GMT

is this ok trans_detail is too big and i dont know how to extract few rows from oracle

Re: Common Date

arulsettu — Tue, 14 Apr 2015 07:41:07 GMT

sorry use this for budget_ins