Skip to main content
Announcements
Qlik Connect 2025: 3 days of full immersion in data, analytics, and AI. May 13-15 | Orlando, FL: Learn More
cancel
Showing results for 
Search instead for 
Did you mean: 
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
its_anandrjs

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;


View solution in original post

13 Replies
its_anandrjs

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;


SunilChauhan
Champion II
Champion II

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,

Sunil Chauhan
SunilChauhan
Champion II
Champion II

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

Sunil Chauhan
PradeepReddy
Specialist II
Specialist II

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)

SunilChauhan
Champion II
Champion II

i am afraid .concatenation will give duplicate rows most probably

Sunil Chauhan
PradeepReddy
Specialist II
Specialist II

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

Not applicable
Author

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,

its_anandrjs

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.

jagan
Luminary Alumni
Luminary Alumni

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.