topic Re: Mapping or left Join in QlikView

Mapping or left Join

bharatkishore — Thu, 14 Feb 2019 09:28:15 GMT

Hi All,

I have two tables. In table A i have Equipment and Posting Date.

In table B i have [Sales Document],
Item ,
Equipment,
Start_DT_New,
End_DT_New,
MaterialEntered

Now for [Sales Document],Item , and MaterialEntered the condition is i need to get only records based on the below condition i.e.

if both the table equipments matches and Posting Date>=Start_DT_New and Posting <=End_DT_New.

Can you please tell me how to achieve this and let me know if you need anything more.

Thanks,

Bharat

Re: Mapping or left Join

pradosh_thakur — Thu, 14 Feb 2019 09:38:28 GMT

Do a mapping load and put a where condition on the resulting table may be.

Re: Mapping or left Join

bharatkishore — Thu, 14 Feb 2019 09:48:56 GMT

I have tried that but the prob is for Ex:
i have in Table 1 i have 1 equipment number has record 1A
in Table 2 i have the same equipment number with two records 1A and 2A the output i will be getting is 1A and 2A even the duplicate reocrds.
But i need only matching records i.e. 1A no duplicate records..

Re: Mapping or left Join

pradosh_thakur — Thu, 14 Feb 2019 09:57:01 GMT

Are you saying Equipment is not the most granular data and something else is?

You need to filter the date condition right? If you need the data that is present in table 1 you can definitely do a left join , apply map will give you the flexibility to enter default value for the non matching rows. At the end you need to see what is feasible for the data set you are using.

Re: Mapping or left Join

bharatkishore — Thu, 14 Feb 2019 10:02:54 GMT

I need to filter based on two conditions
1) when both the equipment matches in both the tables
2) when posting date is >=Startdate and posting date is <=Enddate. Hope i am clear..

Re: Mapping or left Join

pradosh_thakur — Thu, 14 Feb 2019 10:12:27 GMT

Do a inner join and put a where condition on the resulting table . I think it will work for your case.

Re: Mapping or left Join

bharatkishore — Thu, 14 Feb 2019 10:16:56 GMT

So mapping is a wrong choice? correct..

Re: Mapping or left Join

pradosh_thakur — Thu, 14 Feb 2019 10:19:44 GMT

if the column you are using for mapping is not the least granular then yes it's not a right choice. Again your case seems like it warrants an inner join.

Re: Mapping or left Join

bharatkishore — Thu, 14 Feb 2019 10:21:28 GMT

OK Thanks. let me try and get back up to you..
I have tried mapping but i am getting duplicate records as well..

Re: Mapping or left Join

Vegar — Thu, 14 Feb 2019 11:50:24 GMT

It sounds to me that you could benefit from using intervalmatch() with extended syntax. Take a look at the Qlik help section. https://help.qlik.com/en-US/qlikview/November2018/Subsystems/Client/Content/QV_QlikView/IntervalMatch_(Extended_Syntax).htm

Re: Mapping or left Join

bharatkishore — Thu, 14 Feb 2019 12:41:26 GMT

@sunny_talwar any help Sunny Bhai..

Re: Mapping or left Join

sunny_talwar — Thu, 14 Feb 2019 12:52:32 GMT

Can you share a sample to show what you have?

Re: Mapping or left Join

bharatkishore — Thu, 14 Feb 2019 13:17:23 GMT

PFA qvw Sunny Bhai.

Here if you filter example 10149573 equipment in chart i am getting data for Start date and end date where my posting date condition is not matching.

Here in this case the posting date is not greater than start date and less than end date.

Please let me know if you need anything more.

Re: Mapping or left Join

sunny_talwar — Thu, 14 Feb 2019 13:30:19 GMT

I am not sure where the join is taking place in the script, but you essentially need an IntervalMatch here just like @Vegar mentioned in his response above. You can join the table after the intervalmatch to bring them all in a single table

Re: Mapping or left Join

bharatkishore — Thu, 14 Feb 2019 13:49:59 GMT

Can i add the code like the below Sunny Bhai..

left join(Revenue_Calc_2)
IntervalMatch(Equipment, [Sales Document],Item,Start_DT_New,End_DT_New,MaterialEntered)
IW75:
LOAD [Sales Document] as Sales.Document_Contract,
Item as Item_Contract,
Equipment,
Date(Date#(Start_DT_New,'YYYY/MM/DD'),'MM/DD/YYYY') as Start_DT_New,
Date(Date#(End_DT_New,'YYYY/MM/DD'),'MM/DD/YYYY') as End_DT_New,
MaterialEntered
FROM
[C:\Import\DT_Contract_2_for_code.csv]
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Revenue_Calc_3:
load *,

if(Posting.Date>=Start_DT_New and Posting.Date<=End_DT_New,'Contract Period','Outside Contract Period' )as Contract_Flag
Resident Revenue_Calc_2;
drop Table Revenue_Calc_2;

Re: Mapping or left Join

sunny_talwar — Thu, 14 Feb 2019 13:59:22 GMT

@bharatkishore wrote:
left join(Revenue_Calc_2)
IntervalMatch(Equipment, [Sales Document],Item,Start_DT_New,End_DT_New,MaterialEntered)
IW75:
LOAD [Sales Document] as Sales.Document_Contract,
Item as Item_Contract,
Equipment,
Date(Date#(Start_DT_New,'YYYY/MM/DD'),'MM/DD/YYYY') as Start_DT_New,
Date(Date#(End_DT_New,'YYYY/MM/DD'),'MM/DD/YYYY') as End_DT_New,
MaterialEntered
FROM
[C:\Import\DT_Contract_2_for_code.csv]
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

The highlighted part doesn't look right. Where is the Post_date in the interval match here? I think it might be easy if you can provide Excel file with one data source which contain Start_DT and End_DT and another data source which include Post_DT and add all the other necessary columns on which you are joining the two on.