How to Merge Table Data

[Shahzad_Ahsan]

Hello Everyone

I have problem in Qlik Sense

I have 2 tables T1 & T2

T1

Date                      Flag

01/Jan/2017               1

02/Jan/2017               0

03/Jan/2017               1

T2

Date                         Flag

02/Jan/2017               1

03/Jan/2017               2

04/Jan/2017               4

I want result Like this

Date                         Flag

01/Jan/2017               1

02/Jan/2017               1

03/Jan/2017               2   

04/Jan/2017               4

Means the dates that are in both T1 & T2 , show the data from T2 else from T1

Please help me.

[maxgro · Accepted solution]

Start with T2 load, then load T1 with a not exists Date.

T2:

load * inline [

Date,                         Flag

02/Jan/2017 ,              1

03/Jan/2017  ,             2

04/Jan/2017   ,            4

];

concatenate (T2)

load * inline [

Date           ,           Flag

01/Jan/2017     ,          1

02/Jan/2017      ,         0

03/Jan/2017       ,        1

]

Where not Exists (Date)

;

[Shahzad_Ahsan]

Hi Massimo

It worked.

Thanks a lot

[Shahzad_Ahsan]

This shows all rows from T2.

I want all the rows from T1.

So what can I do ??

[maxgro]

What's the result you want with T1 and T2 tables?

[Shahzad_Ahsan]

Suppose T1 has 100 rows and T2 has only 20 rows.

I want all the 100 rows of T1. Those dates which are common in both, show their flag from T2. else show the flag from T1.

I want the result like this:

Date                         Flag

01/Jan/2017                    1

02/Jan/2017               1

03/Jan/2017               2  

[maxgro]

- load T1

- add the T2 records with a left join, so the result is a table with T1 records and 2 flag fields,

Flag for T1 and Flag2 for T2

- do a resident load of the T1 table; pick Flag2 if not null as your Flag Field, else pick Flag as your Flag Field

T1:

load * inline [

Date           ,           Flag

01/Jan/2017     ,          1

02/Jan/2017      ,         0

03/Jan/2017       ,        1

];

//T2:

left Join (T1)

load Date, Flag as Flag2 inline [

Date,                         Flag

02/Jan/2017 ,              1

03/Jan/2017  ,             2

04/Jan/2017   ,            4

];

RENAME Table T1 to tmp;

T1:

load

Date,

Alt(Flag2, Flag) as Flag

Resident tmp;

DROP Table tmp;

[Shahzad_Ahsan]

I could not get this, I have never used resident.

It is showing error "tmp" table not found.

This is my last expression:

LOAD

    "Date",

    MainFlag

FROM [lib://Excel/T2.xlsx]

(ooxml, embedded labels, table is Sheet1);

concatenate 

LOAD

    "Date",

    Flag

FROM [lib://Excel/T1.xlsx]

(ooxml, embedded labels, table is Sheet1)

Where not Exists (Date);

[maxgro]

for your second question

Suppose T1 has 100 rows and T2 has only 20 rows.

I want all the 100 rows of T1. Those dates which are common in both, show their flag from T2. else show the flag from T1.

I want the result like this:

Date                        Flag

01/Jan/2017                    1

02/Jan/2017              1

03/Jan/2017              2 

I didn't use a concatenate but a left join

I added some comments

T1:

load * inline [

Date          ,          Flag

01/Jan/2017    ,          1

02/Jan/2017      ,        0

03/Jan/2017      ,        1

];

//T2:

left Join (T1)

load Date, Flag as Flag2 inline [

Date,                        Flag

02/Jan/2017 ,              1

03/Jan/2017  ,            2

04/Jan/2017  ,            4

];

RENAME Table T1 to tmp;               // rename the in memory table T1 to tmp

T1:

load

Date,

Alt(Flag2, Flag) as Flag

Resident tmp;                                   // read from the in memory table tmp (resident = load from an in memory table),

                                                         // into the in memory table T1

DROP Table tmp;

[Shahzad_Ahsan]

Wow.....It worked

Thank You so much Massimo