topic Re: Concatenate Issues in QlikView

Concatenate Issues

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

Hi all,

Good Morning.

I have two tables which i want to concatenate.

table1: table2:

week, week,

Year Year

But i need only table 1 Year.

How to achieve it.

Regards,

Joshua.

Re: Concatenate Issues

arulsettu — Thu, 14 Jul 2016 05:55:18 GMT

like this

table1:

week,

Year

concatenate (table1)

table2:

week,

Year

Re: Concatenate Issues

Thu, 14 Jul 2016 05:57:34 GMT

Hi,

For what reason you want to concatenate, Do you only want to have records which are available in Table 1

Re: Concatenate Issues

MindaugasBacius — Thu, 14 Jul 2016 06:00:21 GMT

If I caught your issue correct you do not need Table 1 field Year. Try this:

Table1:
Load Week
     ,Year
     ,Other_fields
From source_Table1;

concatenate (Table1)
Load Week
     ,Other_fields
From Source_Table2;

Re: Concatenate Issues

settu_periasamy — Thu, 14 Jul 2016 06:00:30 GMT

Try this

T1:

Load Week, Year from source1;

Concatenate (T1)

Load Year,Week from source2 where exists (Year);

Re: Concatenate Issues

ajsjoshua — Thu, 14 Jul 2016 06:06:47 GMT

Hi,

Actually in table 1 year (2014,2015,2016)

but in table 2 year(2014,2015,2016,2017,2018)

I want only (2014,2015,2016)

Re: Concatenate Issues

ajsjoshua — Thu, 14 Jul 2016 06:08:04 GMT

Hi settu_periasamy

Its affecting other fields .

Some fields depends on the year field.

Thanks and Regards,

Joshua.

Re: Concatenate Issues

ajsjoshua — Thu, 14 Jul 2016 06:08:35 GMT

Hi mindaugasbacius

Its affecting other fields .

Some fields depends on the year field.

Thanks and Regards,

Joshua.

Concatenate Issues

Re: Concatenate Issues

arulsettu — Thu, 14 Jul 2016 06:11:04 GMT

try this

table1:

week,

Year

concatenate (table1)

table2:

week,

Year

where noth match(Year,'2017','2018');

Re: Concatenate Issues

Thu, 14 Jul 2016 06:23:17 GMT

Hi,

When you need only the data wrt to year in table 1, you can go for a left join.

T1:

Load * INLINE [

Week, Year

1, 2014

2, 2015

3, 2016

];

NoConcatenate

T2:

LOAD * INLINE [

Week, Year

1, 2014

2, 2015

3, 2016

4, 2017

5, 2018

];

Left Join(T1)

Load

Year,

Week as W1

Resident T2;

Drop Tables T2;

Re: Concatenate Issues

ajsjoshua — Thu, 14 Jul 2016 06:25:40 GMT

Hi,

If i use Left Join my other fields are affecting,

If i use concatenate my values are not affected only i have problems with Year.

Re: Concatenate Issues

PrashantSangle — Thu, 14 Jul 2016 06:27:45 GMT

Hi,

can you explain your requirement with some data with expected output.

Regards

Re: Concatenate Issues

Thu, 14 Jul 2016 06:34:38 GMT

Hi,

Am not sure is this what you are expecting it to be

T1:

Load * INLINE [

Week, Year, H

1, 2014,3

2, 2015,4

3, 2016,5

];

T2:

LOAD * INLINE [

Week, Year1, I

1, 2014,7

2, 2015,8

3, 2016,4

4, 2017,2

5, 2018,1

];

Concatenate(T1)

Load

Year1,

Week,

Resident T2

Where Exists(Year,Year1);

Drop Tables T2;

Re: Concatenate Issues

effinty2112 — Thu, 14 Jul 2016 06:38:39 GMT

Hi joshua,

After loading table1 in the data model and without having table2 loaded already:

Concatenate (table1)

Load

week,

Year

From ...SourceOftable2 Where Exists(Year);

You would need to check if the field Year exists in tables other than table1, other wise QV will check against existing values of Year in those tables too.

Good luck

Andrew

Re: Concatenate Issues

Anonymous — Thu, 14 Jul 2016 06:44:13 GMT

like this

T1:

LOAD * INLINE [

Week, Year

10, 2015

20, 2016

30, 2014

];

NoConcatenate

T2:

LOAD * INLINE [

Week, Year

01, 2013

02, 2011

03, 2012

];

NoConcatenate

NewTab:

load Week,Year Resident T1

;

Concatenate

load Week Resident T2 ;

drop Tables T1,T2;

Re: Concatenate Issues

ajsjoshua — Thu, 14 Jul 2016 06:46:24 GMT

Hi all,

PFA

I want the two tables concatenated .

My calendar is Week and Year of first table in which i dont want to show year 2017.

Re: Concatenate Issues

Thu, 14 Jul 2016 06:52:04 GMT

Hi,

See this

T1:

LOAD LITM,

Market,

Week,

Year,

sales

FROM

(ooxml, embedded labels, table is Sheet1);

Concatenate(T1)

LOAD LITM,

Market,

cusWeek,

cusYear,

orders

FROM

(ooxml, embedded labels, table is Sheet2)

where exists(Year,cusYear);

Re: Concatenate Issues

PrashantSangle — Thu, 14 Jul 2016 07:04:54 GMT

Hi,

then you want to show or not the entire rows having year 2017?

what you want to do with that rows replace with any value?

try below code which remove entries for year 2017

T1:

LOAD LITM,

Market,

Week,

Year,

sales

FROM

(ooxml, embedded labels, table is Sheet1);

Concatenate(T1)

LOAD LITM,

Market,

cusWeek as WEEK,

cusYear as Year,

orders

FROM

(ooxml, embedded labels, table is Sheet2)

where cusYear<>2017;

Regards

Re: Concatenate Issues

ajsjoshua — Thu, 14 Jul 2016 07:07:54 GMT

Hi effinty2112

I loaded like this

Concatenate (table1)

Load

week,

Year

From ...SourceOftable2 Where Exists(Year);

Now i have other fields in table 2 shall i concatenate with table 1 without week ,year

or i concatenate with table 1 with week ,year

Re: Concatenate Issues

Anonymous — Thu, 14 Jul 2016 07:14:39 GMT

Do you want to avoid future years?

It will impact on data as you are restricting to current year.

else try in front end .

T1:

LOAD LITM,

Market,

Week,

Year,

sales

FROM

(ooxml, embedded labels, table is Sheet1);

LOAD LITM,

Market,

cusWeek as Week,

cusYear as Year,

orders

FROM

(ooxml, embedded labels, table is Sheet2) where Year(Today()) <=year(cusYear);