topic Re: Help with Script in QlikView

Help with Script

Wed, 09 Aug 2017 16:06:04 GMT

Qlik Comrades please help. I have 4 .mdb source documents that I am using in my script. For my first sheet I am combining 2 sources to create one table and the remaining 2 sources to create a second table on my second sheet by using Left Join, no problem. For my third sheet I need to pull all four sources ( currently 2 tables) into one table. What is the correct approach? Everything I have tried so far has rendered incorrect results. Thanks in advance for your help!

Re: Help with Script

Anil_Babu_Samineni — Wed, 09 Aug 2017 16:47:21 GMT

Sheet means Tab?

First tab

mdb1:

Load * From mdb1;

Left Join(mdb1)

Load * From mdb2;

Second Tab

Left Join(mdb1)

Load * From mdb3;

Third Tab

Left Keep (mdb1) // or Left Join (mdb1)

Load * From mdb4;

Re: Help with Script

tomasz_tru — Wed, 09 Aug 2017 16:48:09 GMT

You usually don't have to join tables in Qlik if you have field names under control.

Can you give us sample data?

Re: Help with Script

Wed, 09 Aug 2017 18:32:59 GMT

Thank you for your response. However, I incorrectly stated my intent.

In the first TAB I need to join the first 2 sources to create one table and the next 2 sources to create a second table. This is what I did:

TAB #1

Table1:

Load

.mdb1

Left Join (Table1)

Table2:

Load

.mdb2

Table3:

Load

.mdb3

Left Join (Table3)

Table4:

Load

.mdb4

TAB#2

NewTable:

This is where I need to combine all 4 Tables (actually 2 tables at this point due to the previous joins)

Re: Help with Script

Wed, 09 Aug 2017 18:35:23 GMT

I have same field names in all for sources that I need to keep that way.

Re: Help with Script

Oleg_Troyansky — Wed, 09 Aug 2017 18:54:52 GMT

Hi Angela,

it sounds to me like your different tables contain the same type of information (for example, they all contain Sales). In this case, you want to concatenate your tables rather than JOIN them:

Tab1:

LOAD * FROM ...

;

CONCATENATE (Tab1)

LOAD * FROM ...

;

Cheers,

Oleg Troyansky

Upgrade your Qlik skills with my book QlikView Your Business: An Expert Guide to Business Discovery with QlikView and Qlik Sense

Re: Help with Script

Wed, 09 Aug 2017 19:31:00 GMT

They do contain the same fields but they are from 4 different sources. If I concatenate it creates an additional row with null field values that skews my record count. Left join works correctly.

Re: Help with Script

Oleg_Troyansky — Wed, 09 Aug 2017 21:38:55 GMT

Hmm, sounds very strange, you might want to double check your results.

If you have the same fields and you use LEFT JOIN, then QlikView will only load those rows where all fields already existed in the first table. Since all fields are the same, they are all used as JOIN key fields - including quantities, prices, etc... So, unless all fields are exactly the same, LEFT JOIN won't include them in the result.

Concatenation shouldn't create another row with NULL values, unless it's coming from one of the 4 tables...

best,

Oleg Troyansky

Re: Help with Script

robert_mika — Thu, 10 Aug 2017 04:14:49 GMT

Could you post an example from each db?

Re: Help with Script

Anil_Babu_Samineni — Thu, 10 Aug 2017 04:18:14 GMT

I don't really understand your intend here

But, For your second table you could use something like

Final:

NoConcatenate

Load * From Source1;

Load * From Source2;

Load * From Source3;

Load * From Source4;