Problem with joining tables in Qlik Sense Enterprise

[jwrobel]

Hi,

Im new to qlik sense and i have a problem with joining tables.

I have to pick only these rows from Arkusz1$ where If exist fix2 equal to FIKS from DOK1$ or if fix2 doesnt exist but fix is equal to FIKS from DOK1$.

LOAD
lp.,
FIKS,
Nazwa,
Opiekun,
Region
FROM [lib://*****/DOK_2022.xls]
(biff, embedded labels, table is DOK1$);

LOAD
"AL-AS",
Rok,
"M-c",
"Fix",
"Nazwa kontrahenta",
"Skrót nazwy",
Województwo,
Oddział as Fix2,
"Obszar sprzedaży",
"Symbol faktury",
"Data faktury",
"F-ra specj.",
"Symbol WZ",
"Wartość WZ",
Grupa,
Podgrupa,
KTM,
"Nazwa towaru",
Ilość,
"Cena po rabacie",
"Wartość netto",
"Podatek VAT",
"Wartość brutto",
Marża,
Region
FROM [lib://*****/Qlik_WZ_marza_2022.xls]
(biff, embedded labels, table is Arkusz1$);

Is it possible to do?

I'm grateful for any help.

[Or · Accepted solution]

Assuming I understood your requirement correctly,

Table1:

LOAD
lp.,
FIKS,
Nazwa,
Opiekun,
Region
FROM [lib://*****/DOK_2022.xls]
(biff, embedded labels, table is DOK1$);

Left Join // Or Left keep as relevant

LOAD
"AL-AS",
Rok,
"M-c",
"Fix",
"Nazwa kontrahenta",
"Skrót nazwy",
Województwo,
Oddział as Fix2,

Coalesce(Oddział ,Fix) as FIKS,
"Obszar sprzedaży",
"Symbol faktury",
"Data faktury",
"F-ra specj.",
"Symbol WZ",
"Wartość WZ",
Grupa,
Podgrupa,
KTM,
"Nazwa towaru",
Ilość,
"Cena po rabacie",
"Wartość netto",
"Podatek VAT",
"Wartość brutto",
Marża,
Region
FROM [lib://*****/Qlik_WZ_marza_2022.xls]
(biff, embedded labels, table is Arkusz1$);

Coalesce will pick the first value that isn't null, so if Fix2 is null it will pick Fix, and in both cases the join will be based on the FIKS field (because of the rename to FIKS). Note that you have some other fields in your tables that share the same name (e.g. Region), so they will also be part of the join. If you don't want that to be the case, you'll have to rename them on one of the two sides so they don't share an identical name.

[jwrobel]

Yes, it works. Thanks!

[nenkie]

Great Post! Thank you for visiting here.

 Emory Patient Portal Login 

[jwrobel]

Hey,

I still have a problem with that loading...

Now my loading script looks like that:

DOK:
LOAD
lp.,
FIKS,
Nazwa,
Opiekun,
Region
FROM [lib://***/DOK_2022.xls]
(biff, embedded labels, table is DOK1$);

MARZA:
LEFT KEEP (DOK)
LOAD
"AL-AS",
Rok,
"M-c",
Mid("M-c",4,3) as Miesiac,
"Fix",
"Nazwa kontrahenta",
"Skrót nazwy",
Województwo,
Oddział as Fix2,
if(IsNull(Coalesce("Fix",[Oddział])),Coalesce([Oddział],Fix),Coalesce("Fix",[Oddział])) as FIKS,
"Obszar sprzedaży",
"Symbol faktury",
"Data faktury",
"F-ra specj.",
"Symbol WZ",
"Wartość WZ" as "Wartość WZ_2021",
Grupa,
Podgrupa,
KTM,
"Nazwa towaru",
Ilość as Ilość_2021,
"Cena po rabacie" as "Cena po rabacie_2021",
"Wartość netto" as "Wartość netto_2021",
"Podatek VAT" as "Podatek VAT_2021",
"Wartość brutto" as "Wartość brutto_2021",
Marża as Marża_2021,
Region as Reg
FROM [lib://***/Qlik_WZ_marza_2021.xls]
(biff, embedded labels, table is Arkusz1$);

I've tried a lot of options, but result every time is same - these tables are joining only by "Fix" or only by "Oddzial". Even "If" statement didn't help. 

 

[MayilVahanan · Accepted solution]

Hi there,

Can you try like below

If(Len(Trim(Oddzial))>0, Oddzial, Fix) as FIKS

[jwrobel]

Hey, 

It works now. Thank you!