Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

Data modeling

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,

1 Solution

Accepted Solutions

Re: Data modeling

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;


13 Replies

Re: Data modeling

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;


chauhans85
Esteemed Contributor

Re: Data modeling

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,

chauhans85
Esteemed Contributor

Re: Data modeling

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

pradeepreddy
Valued Contributor II

Re: Data modeling

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)

chauhans85
Esteemed Contributor

Re: Data modeling

i am afraid .concatenation will give duplicate rows most probably

pradeepreddy
Valued Contributor II

Re: Data modeling

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

Not applicable

Re: Data modeling

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

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.

MVP
MVP

Re: Data modeling

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.

Community Browser