topic Re: Data joining in QlikView in QlikView

Data joining in QlikView

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

Hi All

I am facing data joining issue.Initially my data has two tables as below:

I used applymap for Region and country columns so as to get full names,but still I am getting table as above.I need something which looks like:

Please help me.....

Re: Data joining in QlikView

sdmech81 — Mon, 30 Jan 2017 14:43:06 GMT

Hi if u are expecting common data plss do inner join like

Table 1

Inner join

Table 2..

But do note that the join I'd should be Site ID and other than that make sure that u alias rest of the other column .

If table 1 is main table then use left join in btn..Don't keep column names same in both tables same..And comment out unwanted columns like u don't want region n country frm table 2 then comment them out.

Sachin

Re: Data joining in QlikView

vishsaggi — Mon, 30 Jan 2017 14:46:49 GMT

Do you have an excel sheet data that depicts what you have in that image to work on it ? Please attach if possible?

Re: Data joining in QlikView

Anonymous — Mon, 30 Jan 2017 15:21:17 GMT

Hi Sachin

I need fields from both tables,otherwise I am not getting respective SALE value if I don't use fields in second table,here AS and ASIA are of same data but are differing in names,

Re: Data joining in QlikView

sdmech81 — Mon, 30 Jan 2017 15:25:05 GMT

Then atleast rename them and join else ur joining 2 tables with key which includes all multiple columns..

It will lead to wrong result..U join based on siteId only..Rest fields in tble 2 rename as per ur wish..

Re: Data joining in QlikView

Anonymous — Mon, 30 Jan 2017 15:51:40 GMT

Check the attached qvw.

I hope you find it helpfull

Re: Data joining in QlikView

vinieme12 — Mon, 30 Jan 2017 15:57:30 GMT

You don't need applymap here!Just Left join both the tables......you already have full country,region and city names in table1 so use the same

Table1:

Region,

country,

city,

AccountNumber,

Model,

desc,

Amount,

SiteID,

SiteID&'_'&AccountNumber as KEY

FromTable_1_SRC;

left join

Table2:

BillingNumber,

Sale,

SiteID&'_'&AccountNumber as KEY

FromTable_2_SRC;

Re: Data joining in QlikView

Anonymous — Mon, 30 Jan 2017 16:16:26 GMT

Hi Sachin/Vineeth

Got upto an extent

but my data have two rows in first table and second table have more rows as shown

but my data is showing like

hope u understood my problem now.

Re: Data joining in QlikView

sdmech81 — Mon, 30 Jan 2017 16:26:05 GMT

Hi,

As per initial table images,

Please try using key I.e combination of site I'd and acct no..

Please read this thread for understanding

Understanding Join, Keep and Concatenate

Re: Data joining in QlikView

Anonymous — Mon, 30 Jan 2017 16:41:47 GMT

I used account number as key,as I can't use combination of both.But,data which have more columns with same Account number are not showing values.It should be like:

I don't understand why these values are missing if there are more rows

Re: Data joining in QlikView

sdmech81 — Mon, 30 Jan 2017 16:53:11 GMT

Hi, please use this key ..SiteID&'_'&AccountNumber as KEY and join the tables..

Those rows might be missing bcs those aact no might be missing in first table..

We need to understand you data granularity.. Unfortunately I am not in front of system.. Please go through that thread to understand how joins work there are even examples as well..

Re: Data joining in QlikView

vinieme12 — Mon, 30 Jan 2017 17:02:14 GMT

please post sample data in excel

Re: Data joining in QlikView

sangeess21 — Mon, 30 Jan 2017 17:27:12 GMT

Check the attached file. Hope it helps you!

Re: Data joining in QlikView

Anonymous — Tue, 31 Jan 2017 07:19:57 GMT

Hi Vineeth

Posted sample data.

If I have multiple account number and billing number rows I am not getting them in pivot table but they are showing connected when I take a list box

Re: Data joining in QlikView

Anonymous — Tue, 31 Jan 2017 07:47:25 GMT

Hi Sangeetha/Imad

I am using QlikView Personal Edition.I am not able to open your files.Thank you

Re: Data joining in QlikView

vinieme12 — Tue, 31 Jan 2017 07:52:22 GMT

FACT:

LOAD Region,

Country,

City,

SiteID,

AccountNumber,

Model,

Desc,

Amount

FROM

(ooxml, embedded labels, table is Sheet1);

JOIN

LOAD

AccountNumber,

BillingNum,

Sale

FROM

(ooxml, embedded labels, table is Sheet2);

Re: Data joining in QlikView

Anonymous — Tue, 31 Jan 2017 08:09:21 GMT

tried the same Vineeth,but still getting null value in sale row for account number having multiple rows

Re: Data joining in QlikView

vinieme12 — Tue, 31 Jan 2017 08:10:17 GMT

can you post your app? what expression are you trying?

Re: Data joining in QlikView

Anonymous — Tue, 31 Jan 2017 08:31:29 GMT

joined two tables and took a pivot table with fields Region,Country,City,Site ID,Account Number,Billing Number,

Model,Desc,Amount,Sale

in expression tab I am just using Amount and Sale directly

Re: Data joining in QlikView

vinieme12 — Tue, 31 Jan 2017 08:38:13 GMT

I'm not able to replicate the problem, please post your app

Preparing examples for Upload - Reduction and Data Scrambling

Uploading a Sample