topic Re: Mapping Two Tables Based on Date logic in QlikView

Mapping Two Tables Based on Date logic

anuradhaa — Wed, 25 Nov 2020 16:16:04 GMT

I'm Using Two Sals to get Data from Two different data sources,

One Table i have,

DateStarted, Name , Rate ( Names are repeating.)

Another Table i have,

Name,Cost, Date (Here also Names are repeating.)

I need to get ,

Name, Rate , and Cost and Group It by DateStartedMonth in Pivot Table.

Note-

Logic is Date of second table need to map with Latest Started Date Passed,

Say Name1 has below started Dates,

2015/05/26

2015/08/30

2015/11/23

And Table two Dates for Name1 as

2015/09/30

Then That record has to map with 2015/08/30 Date.

Thanks,

Re: Mapping Two Tables Based on Date logic

sunny_talwar — Fri, 15 Apr 2016 10:11:35 GMT

Can you provide your current data for the two tables in a Excel file. I am not sure how the data looks like. But from what I understand you might need a Interval Match

Re: Mapping Two Tables Based on Date logic

swuehl — Fri, 15 Apr 2016 10:13:52 GMT

You could consider creating a date interval from your single DateStarted field:

Creating a Date Interval from a Single Date

Then use e.g. IntervalMatch to map your Date field values into the Rate intervals using Name as a key.

Re: Mapping Two Tables Based on Date logic

anuradhaa — Fri, 15 Apr 2016 10:21:24 GMT

See Attached xsl for data

Re: Mapping Two Tables Based on Date logic

anuradhaa — Fri, 15 Apr 2016 10:28:51 GMT

See attached for data and the results that i need

Re: Mapping Two Tables Based on Date logic

anuradhaa — Fri, 15 Apr 2016 10:29:47 GMT

See attached for data and what i want

Re: Mapping Two Tables Based on Date logic

sunny_talwar — Fri, 15 Apr 2016 10:38:43 GMT

The dates on the SQL2 tab of the Excel contain 2014 information vs. 2015 + information in SQL1 tab. How do you intend to visualize that? Can you input values into the output you are expecting to see?

Re: Mapping Two Tables Based on Date logic

anuradhaa — Fri, 15 Apr 2016 10:48:54 GMT

First Priority Goes to Tab1(Sql1). If there are no records for the dates in Tab2(Sql2) ignore those

Re: Mapping Two Tables Based on Date logic

swuehl — Fri, 15 Apr 2016 10:54:52 GMT

Maybe like this using above referenced approach:

SET DateFormat  = 'MM/DD/YYYY';


Tmp_Rates:
LOAD DATE_Started,
    Name,
    LEASE_RATE,
    Customer
FROM

(biff, embedded labels, table is SQL1$);


Let vEndTime =MakeDate(2020,12,31);


Rates:
LOAD Customer, LEASE_RATE, DATE_Started, Name,
  Date(If(Customer=Peek(Customer),Peek(DATE_Started)-0.00000001, $(#vEndTime))) as DATE_End
Resident Tmp_Rates
Order By  Name, Customer, DATE_Started Desc;


Drop Table Tmp_Rates;






Cost:
LOAD Nmae as Name,
    year,
    month,
    MakeDate(year, month) as Date,
    COST
FROM

(biff, embedded labels, table is SQL2$);


JOIN
IntervalMatch (Date, Name)
LOAD DATE_Started, DATE_End, Name Resident Rates;

Customer	Date	Sum(COST)	Sum(LEASE_RATE)
		855	5021
Cus A	08/01/2015	855	952
Cus A		0	2268
CUSB		0	797
CusB		0	1004

Re: Mapping Two Tables Based on Date logic

sunny_talwar — Fri, 15 Apr 2016 10:56:57 GMT

Try the attached:

Re: Mapping Two Tables Based on Date logic

anuradhaa — Fri, 15 Apr 2016 14:32:42 GMT

Most of the things are correct. but It is wrong when dates are like below

Table1

DATE_Started	Name	LEASE_RATE	Customer
01/01/16	Name1	1025	Cus A
01/11/16	Name1	1000	Cus A
02/19/16	Name1	1551	Cus A
04/11/16	Name1	789	Cus A

Table 2

Nmae	year	month	COST
Name1	2013	10	263.88

Out put

DATE_Started	Name	EndDate	Date	LEASE_RATE	COST	month	year
01/01/16	Name1	4/15/2016	10/1/2013	1025	263.88	10	2013
01/11/16	Name1	4/15/2016	10/1/2013	1000	263.88	10	2013
02/19/16	Name1	4/15/2016	10/1/2013	1551	263.88	10	2013
04/11/16	Name1	4/15/2016	10/1/2013	789	263.88	10	2013

Re: Mapping Two Tables Based on Date logic

sunny_talwar — Fri, 15 Apr 2016 14:39:08 GMT

In this case, I would do just a straight join between the two tables. But this seems like a different requirement now. How would this change if you have two entries in Table 2?

Name, year, month, COST

Name1, 2013, 10, 263.88

Name1, 2014, 02, 270.21 -> Randomly adding something

What is going to be the output now?

Re: Mapping Two Tables Based on Date logic

anuradhaa — Fri, 15 Apr 2016 14:53:59 GMT

Then,

we don't need to join that record, Since those dates doesn't inside date range in SQL1.

Output

SHIP_DATE	Name	EndDate	cleaning_cost	Date	LEASE_RATE	repair_amount	repair_month	repair_year
01/01/16	Name1	4/15/2016	0		1025
01/11/16	Name1	4/15/2016	0		1025
02/19/16	Name1	4/15/2016	0		1025
04/11/16	Name1	4/15/2016	0		1025

Re: Mapping Two Tables Based on Date logic

sunny_talwar — Fri, 15 Apr 2016 14:58:15 GMT

So I am assuming that a single database will have different Name1 and based on different Names you are going to decide which type of join is needed? I guess it would be helpful to look at a more comprehensive sample which include both these cases combined with the expected output.

Re: Mapping Two Tables Based on Date logic

anuradhaa — Wed, 20 Apr 2016 06:22:37 GMT

Hi Sunny,

I think Most of the Things are correct , but i feel interval match doesn't apply when i'm loading data. I have get excel out put and load that excel to my script and it works fine, but the interval match doesn't work when it is loading from DataBase.

See my Script from second table,

LOAD *,

Date(MakeDate(r_year, r_month)) as Date;

select

r.C + ' ' + cast(r.CNum as varchar(6)) as 'RR',

month(h.rdate) as 'r_month',

year(h.rdate) as 'r_year'

from Rr r

Left Join (r)

IntervalMatch (Date, RR)

LOAD *

Resident Ss;

Right Join (r)

LOAD *

Resident Ss;

DROP Table Ss;

Re: Mapping Two Tables Based on Date logic

sunny_talwar — Wed, 20 Apr 2016 08:51:46 GMT

I don't really follow your script here. Where exactly is the interval match taking place? Interval Match is used when you are linking a date range to a particular date. Where is your date range?

Left Join (r)

IntervalMatch (Date, RR)

LOAD StartDate,

EndDate,

Resident Ss;