topic Data Model related Query in QlikView

Data Model related Query

Fri, 04 Jul 2014 04:55:27 GMT

Hi ,

I have two tables and one table is having data of 1st department and another table is having data of 2nd depatment .

there are two field are common in both

month and conseioners name .

and i want to combine these two show total sale of both the depatment month wise and conssioners wise,

Deparment 1:

how should i combine these two tables so that there is loop in this and will show perfect data model ?

Depatment1:

MONTH_YEAR_OF_SALE,

Month( Date#(Left(MONTH_YEAR_OF_SALE,2) & '/' & mid(MONTH_YEAR_OF_SALE,4,2) & '/' & mid(MONTH_YEAR_OF_SALE,7,4),'DD/MM/YYYY')) as Month,

SALE

START_DATE as START_DATE_FF,

END_DATE as END_DATE_FF,

INSERT_DATE,

SERVICE_PROVIDER as Consessioner,

Department 2:

Date#(Left(DATE_OF_DELIVERY,2) & '/' & mid(DATE_OF_DELIVERY,4,2) & '/' & mid(DATE_OF_DELIVERY,7,4),'DD/MM/YYYY') as Date,

Month( Date#(Left(DATE_OF_DELIVERY,2) & '/' & mid(DATE_OF_DELIVERY,4,2) & '/' & mid(DATE_OF_DELIVERY,7,4),'DD/MM/YYYY')) as Month,

Sale,

CATEGORY as Consessioner,

Re: Data Model related Query

Fri, 04 Jul 2014 05:02:46 GMT

you might want to consider Autonumber?

Re: Data Model related Query

aveeeeeee7en — Fri, 04 Jul 2014 05:18:02 GMT

There are 2 ways to do this:

1) First Method. Use Link Tables:

Depatment1:

Load

MONTH_YEAR_OF_SALE,

Month( Date#(Left(MONTH_YEAR_OF_SALE,2) & '/' & mid(MONTH_YEAR_OF_SALE,4,2) & '/' & mid(MONTH_YEAR_OF_SALE,7,4),'DD/MM/YYYY')) as Month,

SALE

START_DATE as START_DATE_FF,

END_DATE as END_DATE_FF,

INSERT_DATE,

SERVICE_PROVIDER as Consessioner,

(MONTH_YEAR_OF_SALE,7,4),'DD/MM/YYYY'))&'/'&SERVICE_PROVIDER AS Comination_Key,

'Table1' AS Flag1

From .......qvd;

Department 2:

Load

Date#(Left(DATE_OF_DELIVERY,2) & '/' & mid(DATE_OF_DELIVERY,4,2) & '/' & mid(DATE_OF_DELIVERY,7,4),'DD/MM/YYYY') as Date,

Month( Date#(Left(DATE_OF_DELIVERY,2) & '/' & mid(DATE_OF_DELIVERY,4,2) & '/' & mid(DATE_OF_DELIVERY,7,4),'DD/MM/YYYY')) as Month,

Sale,

CATEGORY as Consessioner,

(DATE_OF_DELIVERY,7,4),'DD/MM/YYYY'))&'/'&CATEGORY AS Comination_Key,

'Table2' AS Flag2

From .......qvd;

Use Combination_Key to show Total Sales and use Flag1 & Flag2 to see data coming from which Table.

2) Second Method. Use Concatenated Table:

Depatment1:

Load

MONTH_YEAR_OF_SALE,

Month( Date#(Left(MONTH_YEAR_OF_SALE,2) & '/' & mid(MONTH_YEAR_OF_SALE,4,2) & '/' & mid(MONTH_YEAR_OF_SALE,7,4),'DD/MM/YYYY')) as Month,

SALE

START_DATE as START_DATE_FF,

END_DATE as END_DATE_FF,

INSERT_DATE,

SERVICE_PROVIDER as Consessioner,

(MONTH_YEAR_OF_SALE,7,4),'DD/MM/YYYY'))&'/'&SERVICE_PROVIDER AS Comination_Key,

'Table1' AS Flag1

From .......qvd;

Concatenate

Department 2:

Load

Date#(Left(DATE_OF_DELIVERY,2) & '/' & mid(DATE_OF_DELIVERY,4,2) & '/' & mid(DATE_OF_DELIVERY,7,4),'DD/MM/YYYY') as Date,

Month( Date#(Left(DATE_OF_DELIVERY,2) & '/' & mid(DATE_OF_DELIVERY,4,2) & '/' & mid(DATE_OF_DELIVERY,7,4),'DD/MM/YYYY')) as Month,

SALE,

CATEGORY as Consessioner,

(DATE_OF_DELIVERY,7,4),'DD/MM/YYYY'))&'/'&CATEGORY AS Comination_Key,

'Table2' AS Flag2

From .......qvd;

Hope that works for you.

Regards

Aviral Nag

Re: Data Model related Query

its_anandrjs — Fri, 04 Jul 2014 05:48:09 GMT

Try one of this ways

Depatment1:

MONTH_YEAR_OF_SALE,

Month( Date#(Left(MONTH_YEAR_OF_SALE,2) & '/' & mid(MONTH_YEAR_OF_SALE,4,2) & '/' & mid(MONTH_YEAR_OF_SALE,7,4),'DD/MM/YYYY')) as MonthField,

SALE

START_DATE as START_DATE_FF,

END_DATE as END_DATE_FF,

INSERT_DATE,

SERVICE_PROVIDER as ConsessionerField,

Month( Date#(Left(MONTH_YEAR_OF_SALE,2) & '/' & mid(MONTH_YEAR_OF_SALE,4,2) & '/' & mid(MONTH_YEAR_OF_SALE,7,4),'DD/MM/YYYY')) &'_'& SERVICE_PROVIDER as %Key

Join or Concatenate here depends on the table behavior according to that you have to use join with(inner,Outer,Left,Right) or Cocatenate

Department 2:

Date#(Left(DATE_OF_DELIVERY,2) & '/' & mid(DATE_OF_DELIVERY,4,2) & '/' & mid(DATE_OF_DELIVERY,7,4),'DD/MM/YYYY') as Date,

Month( Date#(Left(DATE_OF_DELIVERY,2) & '/' & mid(DATE_OF_DELIVERY,4,2) & '/' & mid(DATE_OF_DELIVERY,7,4),'DD/MM/YYYY')) as Depart_Month,

Month( Date#(Left(DATE_OF_DELIVERY,2) & '/' & mid(DATE_OF_DELIVERY,4,2) & '/' & mid(DATE_OF_DELIVERY,7,4),'DD/MM/YYYY')) &'_'& CATEGORY as %Key

Sale,

CATEGORY as Depart_Consessioner

Note:- If use concatenate the fields name same but not necessarily but you have to check out put of the table and the field name in the table must be same name. If using joins create key field like Month &'_'& Consessioner as %Key in both the tables.

Re: Data Model related Query

SunilChauhan — Fri, 04 Jul 2014 06:01:37 GMT

if second tables Consessioner field is subet of firstable Consessioner field

then use left join b/w two tables.

or you can use what anand chouhan suggeted but keep to seprate table department 1 linked to other with composite key

concatenate in this case if both table have same Consessioner then might duplicate

or Most used

create a fact table using tables dept 1 and dept2 with composite key and link these tables to this fact table

its makes star schema for you. you need to take care of synthetic key

hope thishelps

Re: Data Model related Query

Tue, 08 Jul 2014 10:45:05 GMT

i have used this % key concept everything is correct but when i m taking sum(Sale)

its not working for dep 1.

its working when i'm making graph by conssioners

Re: Data Model related Query

Tue, 08 Jul 2014 10:55:14 GMT

DEp1:

LOAD
Date#(Left(DATE_OF_DELIVERY,2) & '/' & mid(DATE_OF_DELIVERY,4,2) & '/' & mid(DATE_OF_DELIVERY,7,4),'DD/MM/YYYY') as Date,
Month( Date#(Left(DATE_OF_DELIVERY,2) & '/' & mid(DATE_OF_DELIVERY,4,2) & '/' & mid(DATE_OF_DELIVERY,7,4),'DD/MM/YYYY')) as Month123,
Month( Date#(Left(DATE_OF_DELIVERY,2) & '/' & mid(DATE_OF_DELIVERY,4,2) & '/' & mid(DATE_OF_DELIVERY,7,4),'DD/MM/YYYY')) &'_'& CATEGORY as %Key,
QUANTITY as sale for dep1,
CATEGORY as Consessioner,

FROM
[ABC]
(qvd);

Join

dep 2:

LOAD
Month(Date#(MONTH_YEAR_OF_SALE, 'MMM YYYY')) &'_'& SERVICE_PROVIDER as %Key,

DUTY_PAID_SALE as sale 1

BONDED_SALE as sale 2
SERVICE_PROVIDER as Consessioner,
TERMINAL
FROM
[GYH]

see my final script .

now see in dep 2 there are two sales field and by addibd both i will get my total sale

so when im making graph on behalf of conseioners its giving correct data

like foe consessioner A:12

// B:124

C:123

but i'm using exp like

sum ( sale1 ) + sum(sale2)

its giving some rendom number

so wat could be the issue

please help ?????????????????