topic Multiple distinct left join in QlikView

Multiple distinct left join

Tue, 19 Jun 2012 08:06:05 GMT

Hi all

Is there the possibility to obtain the same result with only one (concatenate or somethings else) "LEFT JOIN"

19/06/2012 5.44.14: 0284 LEFT JOIN

19/06/2012 5.44.14: 0285 load

19/06/2012 5.44.14: 0286 DISTINCT(WKNREC),

19/06/2012 5.44.14: 0287 IF(OnlyInb = 'U',WKCATA,WKNQCD) AS Campagna

19/06/2012 5.44.14: 0288 resident TableOrders

19/06/2012 5.50.35: 029 LEFT JOIN

19/06/2012 5.50.35: 0293 load

19/06/2012 5.50.35: 0294 DISTINCT(WKNREC),

19/06/2012 5.50.35: 0295 (WKA0CD & '-' & Campagna) as KPROMOTIONGRSU

19/06/2012 5.50.35: 0296 resident TableOrders

Many Thanks for your reply

Re: Multiple distinct left join

Anonymous — Tue, 19 Jun 2012 12:57:56 GMT

Would this work for you?

LEFT JOIN
load 
DISTINCT(WKNREC),
IF(OnlyInb = 'U',WKCATA,WKNQCD) AS Campagna,
(WKA0CD &  '-' & IF(OnlyInb = 'U',WKCATA,WKNQCD)) as KPROMOTIONGRSU
 resident TableOrders

-Phil

Re: Multiple distinct left join

Tue, 19 Jun 2012 13:20:20 GMT

Hi Phil

Thank you for your replay.

You reply is correct and work perfectly.

My question was little more complicate.

In this case the example was easy, but in a complicate case is useful write multiple load:

There is a way to use multiple load (during the same left join) something like this ?

LEFT JOIN

load

DISTINCT(WKNREC),

WKA0CD & '-' & Campagna) as KPROMOTIONGRSU

load

DISTINCT(WKNREC),

IF(OnlyInb = 'U',WKCATA,WKNQCD) AS Campagna

resident TableOrders

Re: Multiple distinct left join

sebastiandperei — Tue, 19 Jun 2012 14:25:38 GMT

Hi cwclod,

In first place, I didn't know about DISTINCT (field), thanks for the data!!

In second one. This way of couple loads is called "Proceed Load" (or similar, my english is not good). The upper Load takes the second Load like source of data. Take in mind that the upper will only READ the registers in lower Load.

Left Join

Load DISTINCT (WKNREC)

WKA0CD&'-'&Campagna as KPROMOTIONGRSU; <- your example doesn't have ';', this is important.

Load DISTINCT (WKNREC)

If (Onlynb = 'U', WKCATA, WKNQCD) as Campagna

Resident TableOrders;

This script will never works. Because in lower Load, you didn't read WKA0CD and WKNREC. This fields are used for the upper Load, and if you don't read in lower Load, the data is inexistent for the upper Load. You could use:

Left Join

Load DISTINCT (WKNREC)

WKA0CD&'-'&Campagna as KPROMOTIONGRSU; <- your example doesn't have ';', this is important.

Load DISTINCT (WKNREC)

WKNREC,

WKA0CD,

If (Onlynb = 'U', WKCATA, WKNQCD) as Campagna

Resident TableOrders;

This pair of sentences will Join (with the last readed table), for each distinct WKNREC, KPROMOTIONGRSU with the combination of WKA0CD, WKCATA and WKNQCD data. But you will not Join WKNREC, WKA0CD, WKCATA, WKNQCD and Campagna.

For this example, the best way is to use Phill's sentence. But if you want to know, I like it be useful for you!!