Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
fcecconi
Partner - Creator III
Partner - Creator III

Best Way How to Solve

I need some help here.

I have 1 table and here are some of the elements

Transaction Date

P_KEY

etc

I want to use the Transaction Date and the P_KEY to find a value in another table that looks like this

P_KEY

Program

Program Start Date

Program End Date

My Final table needs to look like this

Transaction Date

P_KEY

Program

Program Start Date

Program End Date

Thanks in advance

1 Solution

Accepted Solutions
maxgro
MVP
MVP

SET DateFormat='MM/DD/YYYY';

Transaction:

load * inline [

Transaction_Date,     P_KEY  

08/01/2016       ,             1  

08/01/2016        ,            2     

08/02/2016         ,           1  

08/02/2016          ,          2       

08/03/2016           ,         1

08/03/2016            ,        2          

08/31/2016           ,         1

];    

Program:

load * inline [

P_KEY ,    Program ,     Program_Start  ,       Program_End

   1   ,            A,                   07/20/2016  ,        08/01/2016

   1    ,           B ,                  08/02/2016   ,       08/31/2016

   2     ,          A  ,                 08/01/2016    ,      08/31/2016

   ];

  

Inner Join

IntervalMatch (Transaction_Date, P_KEY)

Load distinct Program_Start, Program_End, P_KEY resident Program;

1.png

View solution in original post

10 Replies
Not applicable

a best pratic its use apply map

Map_Td:

maping load

P_KEY,

Transaction Date

from ....


table:

load

P_KEY

Program

Program Start Date

Program End Date

applymap('Map_Td',P_KEY,'not found') as Transaction Date

from ...

jpenuliar
Partner - Specialist III
Partner - Specialist III

Sounds like you wanted to JOIN your tables.

fcecconi
Partner - Creator III
Partner - Creator III
Author

Sorry that I forgot to post sample data!


Transaction Table

Transaction_Date     P_KEY   

08/01/2016                    1   

08/01/2016                    2      

08/02/2016                    1   

08/02/2016                    2        

08/03/2016                    1 

08/03/2016                    2           

...

08/31/2016                    1     

Program Table

P_KEY     Program      Program_Start         Program_End

   1               A                   07/20/2016          08/01/2016

   1               B                   08/02/2016          08/31/2016

   2               A                   08/01/2016          08/31/2016




Final Table

Transaction_Date     P_KEY     Program      Program_Start          Program_End

08/01/2016                    1               A                   07/20/2016          08/01/2016

08/01/2016                    2               A                   08/01/2016          08/31/2016

08/02/2016                    1               B                   08/02/2016          08/31/2016

08/02/2016                    2               A                   08/01/2016          08/31/2016

08/03/2016                    1               B                   08/02/2016          08/31/2016

08/03/2016                    2               A                   08/01/2016          08/31/2016

...

08/31/2016                    1               B                    08/02/2016          08/31/2016

I need to see where the transaction date falls in between the program start and program end date for a give P_KEY

sudeepkm
Specialist III
Specialist III

your second table does not have Transaction Date and only P_KEY is common between both the tables.

To get the respective results from second tables based on P_KEY and Transaction Date you may need to have the same fields or fields carrying matching records in the second table.

Once you find them you can JOIN them by selecting a JOIN type (outer, inner, left or right) based on your requirement.

Anil_Babu_Samineni

Can you please post sample Data

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Anil_Babu_Samineni

Some Corrections, Tell me if i am wrong

First:

Mapping Load

Transaction Date

P_KEY

etc

Second:

P_KEY,

Applymap('First',Trim([Transaction Date])) as [Transaction Date]

Program

Program Start Date

Program End Date

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
sudeepkm
Specialist III
Specialist III

you can check interval match in QlikView.

sudeepkm
Specialist III
Specialist III

Please take a look at the attached solution.

maxgro
MVP
MVP

SET DateFormat='MM/DD/YYYY';

Transaction:

load * inline [

Transaction_Date,     P_KEY  

08/01/2016       ,             1  

08/01/2016        ,            2     

08/02/2016         ,           1  

08/02/2016          ,          2       

08/03/2016           ,         1

08/03/2016            ,        2          

08/31/2016           ,         1

];    

Program:

load * inline [

P_KEY ,    Program ,     Program_Start  ,       Program_End

   1   ,            A,                   07/20/2016  ,        08/01/2016

   1    ,           B ,                  08/02/2016   ,       08/31/2016

   2     ,          A  ,                 08/01/2016    ,      08/31/2016

   ];

  

Inner Join

IntervalMatch (Transaction_Date, P_KEY)

Load distinct Program_Start, Program_End, P_KEY resident Program;

1.png