How to filter a data of another table with one other table in QlikView ?

[vijaiks]

Hi,

Totally I have two tables in QlikView. One table is Month_Table having one column named as Month in 'MMM-YY' format.

Month

Jan-20

Feb-21

Oct-22

May-23

In the another table named phase_wise, I have three columns,

Start	End	Tier
Nov-16	Oct-17	Tier 1
Nov-17	Oct-18	Tier 2
Nov-18	Oct-19	Tier 3
Nov-19	Oct-20	Tier 4
Nov-20	Oct-21	Tier 5
Nov-21	Oct-22	Tier 6
Nov-22	Oct-23	Tier 7

Now I have created a Straight table in chart, in that I want to show one column, compare the Month values with Start and End and want to identify which Tier it is locating. Final Output I need is like the Tier. I don't want to do any modifications in script like IntervalMatch or ApplyMap functions. I want to write this in the expression.

I wrote a expression like: If(Month >= Start and Month <= End, Tier)

But this isn't work as expected and also tried with formattings like Date(Month, 'MMM-YY') and Date(Date#(Month, 'MMM-YY'), 'MMM-YY'). And also Aggr function and Only function in expression also not worked because here we are giving the whole column like Start and End in the comparison.

Please help me in this issue in QlikView. QlikView 

Thanks in Advance.

Regards,

Vijai

[Henry_Laser]

Hi,

Do you have to have two tables? Have you attempted to add the month data in your load for your phase_wise table?

[vijaiks]

Hi,

I have don't have attempted to add Month data to the other table while Load. Because here I have displayed the Sample data. But in the Original data I have lot of columns associated with the Month data. While doing IntervalMatch or ApplyMatch, lot of mismatched and duplicated data was added as rows. 

Some synthetic keys and extra ugly data found while doing this IntervalMatch etc., So, I tried and left that attempt. 

[Henry_Laser]

Hi,

Can you show me a sample of your load for phase_wise. The reason why you might have miss matches is due to the 2 tables not being linked. I just want to see what you are seeing for me to better understand.

[vijaiks]

Hi,

This is the loading statement for the two tables.

[Henry_Laser]

Thank you for the above.

In your Phase_Wise table, add a field with the following: month(date(Start,'MMM-YY')) AS Start_Month and 

month(date(End,'MMM-YY')) AS End_Month. This should then give you the ability to use your original function with the following:

If(Start_Month >= Start and End_Month <= End, Tier).

[vijaiks]

Hi,

I don't want the month alone to be taken as Start_Month and End_Month. Because with the combination of both like Month and Year, I want to identify the Month column contains (MMM-YY) in Month_Table located in which Tier in phase_wise table.

Thanks for the understandings.

Regards,

Vijai