topic Re: Data modeling in QlikView

Data modeling

Fri, 04 Jul 2014 04:56:22 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 modeling

its_anandrjs — Fri, 04 Jul 2014 05:04:58 GMT

What happen when you join this two tables by field Month and Consessioner did you try or in association state also.

Depatment1:

Load * ,

Month &'_'& Consessioner

From Location;

Join// ( Inner Join, Outer Join)

Depatment2:

Load * ,

Month &'_'& Consessioner

From Location;

Re: Data modeling

SunilChauhan — Fri, 04 Jul 2014 05:18:26 GMT

use left join as i can see department 1 should be you master data

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:

left JOIN(Depatment1)

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 modeling

SunilChauhan — Fri, 04 Jul 2014 05:20:03 GMT

you can also apply anand suggetsion but you need to comment or rename Month and Consessioner in one of them other wise it will create synthetic key

Re: Data modeling

PradeepReddy — Fri, 04 Jul 2014 05:20:53 GMT

Try to concatenate the tables, so that you will get the resultant data in single table.

Create a chart with..

Dimension: Month

Expression : Sum(Sale)

Re: Data modeling

SunilChauhan — Fri, 04 Jul 2014 05:24:00 GMT

i am afraid .concatenation will give duplicate rows most probably

Re: Data modeling

PradeepReddy — Fri, 04 Jul 2014 05:29:52 GMT

either we can use the Join/Concatenation based on the requirements in other charts as well.

Re: Data modeling

Fri, 04 Jul 2014 05:35:03 GMT

Hi Yogita,

Concatenate both the tables, jus make sure the column names match

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,

concatenate

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 modeling

its_anandrjs — Fri, 04 Jul 2014 05:42:03 GMT

Try this way previous suggestion is just sample code for syntax

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. If using joins create key field like Month &'_'& Consessioner as %Key in both the tables.

Re: Data modeling

jagan — Fri, 04 Jul 2014 05:58:34 GMT

Hi,

Try like this

Data:

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

FROM DataSource1;

Concatenate(Data)

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

FROM DataSource2;

Regards,

Jagan.

Re: Data modeling

Fri, 04 Jul 2014 07:17:02 GMT

i tried to join by conssioner then its not giving correct data month wise .. if i will make a key on the behalf of conssioner and month then wat wil happen . i dun want to concatente this tables bcoz they both have diffrent fields other dn month and conssioners

Re: Data modeling

Fri, 04 Jul 2014 09:27:14 GMT

i more thing these two sales of depratment 1 and 2 are different . i dont want to combine them .

then what should i use . concatenate or join ??

Re: Data modeling

Fri, 04 Jul 2014 10:16:47 GMT

depatment 1:

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,

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

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

//DATE_OF_DELIVERY,

//FLT_NO,

// AIRCRAFT_TYPE,

// TO_DEST,

QUANTITY as Sale,

Month &'_'& Consessioner as Key,

// BAY_NO,

// DISP_POSS_TIME,

// FUELLING_STARTED,

//FUELLING_FINISHED,

// DISP_DISPOSS_TIME,

// FUELLING_TIME,

// REMARKS,

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

CATEGORY as Consessioner

//BONDED_DUTY_PAID

FROM

SOurce XYZ,

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,

SALE1,

SALE2,

//START_DATE as START_DATE_FF,

// END_DATE as END_DATE_FF,

//INSERT_DATE,

SERVICE_PROVIDER as Consessioner,

// 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

Month &'_'& Consessioner as Key

FROM

Source 2.

but its not working.. kindly help???

Re: Data modeling

Tue, 28 Apr 2015 13:43:45 GMT

the field with data should be one,

so on department 1:

replace Sale with SALE1.

QUANTITY as SALE1 //Sale