topic Best Way How to Solve in QlikView

Best Way How to Solve

fcecconi — Wed, 03 Aug 2016 17:30:24 GMT

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

Re: Best Way How to Solve

Wed, 03 Aug 2016 17:36:29 GMT

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 ...

Re: Best Way How to Solve

jpenuliar — Wed, 03 Aug 2016 17:39:53 GMT

Sounds like you wanted to JOIN your tables.

Re: Best Way How to Solve

fcecconi — Wed, 03 Aug 2016 17:52:17 GMT

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

Re: Best Way How to Solve

sudeepkm — Wed, 03 Aug 2016 17:59:35 GMT

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.

Re: Best Way How to Solve

Anil_Babu_Samineni — Wed, 03 Aug 2016 18:00:48 GMT

Can you please post sample Data

Re: Best Way How to Solve

Anil_Babu_Samineni — Wed, 03 Aug 2016 18:03:36 GMT

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

Re: Best Way How to Solve

sudeepkm — Wed, 03 Aug 2016 18:11:54 GMT

you can check interval match in QlikView.

Re: Best Way How to Solve

sudeepkm — Wed, 03 Aug 2016 18:51:06 GMT

Please take a look at the attached solution.

Re: Best Way How to Solve

maxgro — Wed, 03 Aug 2016 19:41:45 GMT

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;

Re: Best Way How to Solve

fcecconi — Wed, 03 Aug 2016 20:16:46 GMT

The transaction date has 2 start and end dates for a given P_KEY (both Program A & B are appearing). I need t have a single row