topic Re: File grows a lot when joining tables in QlikView

File grows a lot when joining tables

paulinhok14 — Wed, 25 Nov 2020 16:16:04 GMT

Guys good morning,

I have a qvw application that its size is about 7MB.

The table layout of my file is as follows:

However, due to an application need, I am doing (at the end of the script) a left join of the Info_Tarefa table to the Frota_Aeronaves table, so I can use a concatenated field between 2 fields that are in the table on the left, and 1 field that is in the table on the right.

By simply doing this left join, my application grows in size absurdly, I've already used NoConcatenate to make sure that Qlik is not keeping the Frota_Aeronaves table in the script, but I do not know what might be happening.

12 million lines are being loaded in my new final table, and my file, with just that snippet of code from the image, goes from 7MB to 133MB ...

Anyone have any idea what might be happening?

Thank you and a hug to everyone!

Re: File grows a lot when joining tables

jonathandienst — Fri, 18 May 2018 13:19:23 GMT

It looks like the table you are joining at the end or Info_Tarefa have non-unique joining keys, causing multiplication of the records.

Your image show only LOAD *, so I cannot give a simple recommendation. Do they need to be joined (rely on association to relate the records - you will need to manage a composite key (syn key) though)?

What I would do is identify all the fields common to Info_Tarefa and the join table. Then load the join table into a separate document, like

LOAD

Field1,

Field2,

....

FieldN,

Field1 & Field2 & ....& FieldN as Key,

1 As Count

FROM ....

(where Field1...N are the common fields)

Create a table with Key as the dimension and Sum(Count) as the metric. Look for values other than one. These are the records causing duplication.

Re: File grows a lot when joining tables

paulinhok14 — Fri, 18 May 2018 13:50:24 GMT

Hello Jonathan, thanks for your answer!

Well, I am left-joining the content from Info_Tarefa into Frota_Aeronaves, but the only field that is common in both tables is Modelo&Tarefa, so why when I make the new table loading all from Frota_Aeronaves I have that big amount of data?

Thats the point I can't understand

Re: File grows a lot when joining tables

olivierrobin — Fri, 18 May 2018 14:00:06 GMT

hello

that means that for 1 value ok a key on the left table, you have more than 1 value of this key on the right table

so you create new rows on your table

eg:

suppose a key with value 'a'

if you have 1 row on the left table and 5 on the right, joining crates 5 rows on the resulting table

Re: File grows a lot when joining tables

paulinhok14 — Fri, 18 May 2018 16:36:56 GMT

Hello Olivier, thanks for your answer.

Now I get what you say., my field Modelo&Tarefa got a lot of different registers on my Frota_Aeronaves table, as the image shows:

(This is only the Frota_Aeronaves display table, before the join)

But let me try to explain you why I am doing this:

My application basically displays what parts (PN) are used in a task (Tarefa) and when (Mes_Falha).

But, in a timeline those datas are not displaying the real scenario for me, because:

First problem: Suppose I have 2 or more different tasks that use the SAME part (PN) and the tasks are being executed in the same month (Mes_Falha), for the same MODEL of aircraft, and a task can have 1 or more PNs. BUT, the tasks will be done by different aircrafts (they have different Serial Numbers, that is a field present in Frota_Aeronaves).

Second Problem: Imagine also that the same aircraft (only one Serial Number), in the same month, uses the same PN, BUT, for different tasks.

If in a chart I just use the expression = Count (PN), he is not considering the situations I meant before, so I wanted to create a concatenated field PN+Modelo&Tarefa+[S/N]

So if I count this field on my chart, it will solve my problem, thing is, PN and Modelo&Tarefa are in the Info_Tarefa table, and S/N and Modelo&Tarefa are in Frota_Aeronaves table, thats why I am trying to join them.

Did you get it? It could be a little bit confusing

If you have another suggestions to solve this kind of problems, I would be glad also!

Re: File grows a lot when joining tables

paulinhok14 — Fri, 18 May 2018 16:43:33 GMT

Hey Jonathan, thanks for adding information, now the problem is clearer in my mind.

As I did with Olivier, I'll try to explain you the real situation and if you have suggestions it would help me so much.

My application basically displays what parts (PN) are used in a task (Tarefa) and when (Mes_Falha).

But, in a timeline those datas are not displaying the real scenario for me, because:

Second Problem: Imagine also that the same aircraft (only one Serial Number), in the same month, uses the same PN, BUT, for different tasks.

If in a chart I just use the expression = Count (PN), he is not considering the situations I meant before, so I wanted to create a concatenated field PN+Modelo&Tarefa+[S/N]

Do you see any other ways to solve this?

Re: File grows a lot when joining tables

jonathandienst — Mon, 21 May 2018 04:52:19 GMT

What happens if you don't join them in the backend (but let them associate)? Use both fields in a front end expression like PN+[Modelo&Tarefa]+[S/N]

Re: File grows a lot when joining tables

sasiparupudi1 — Mon, 21 May 2018 07:01:15 GMT

If only 1 field is require for the complex field, why dont you use a mapping load on the right table and use applymap on the left table ?

hth

Re: File grows a lot when joining tables

paulinhok14 — Mon, 21 May 2018 13:11:52 GMT

Hello Jonathan, thanks in advance!

Well I didn't even know it is possible to do. Also I tried to do this and the results are:

Seems to be better than I did before, but, there are some considerable points....

There are some tasks that haven't PNs associated and in a PN count this should be not considered

In my expression, I tried something like if ( not isNull (PN), Count (PN&[Modelo&Tarefa]&[S/N] ) )

Didn't work, so I applied in my dimension the conditional if ( not isNull (PN), Dimension).

Apparently it works, am I doing it the right way?

Re: File grows a lot when joining tables

paulinhok14 — Mon, 21 May 2018 13:15:49 GMT

Hello Sasidhar, thanks for your answer.

I am relatively new in QlikView development and I dont know what is mapping load, but in the other way, thank you for the advice, I'll search for this method and how to use it, if it works I will use for sure.

Kind regards!