How to get similar outcome?

[#1]

Hi guys,

hope you can help. I have this script:

T1:

LOAD * Inline [

F1, F2

a, 1

a, 2

b, 3

c, 4

];

T2:

LOAD DISTINCT * Inline [

F1, F2

a, 1

b, 3

b, 3

c, 4

];

I need to have this result:

F1     F2

a        1

a        2

b        3

c        4

a        1

c        4

b        3

It should only have one table in the data model with the content as shown above. Data should not be change in the two sources only the Load script.

hope someone can help. thanks.

[ashfaq_haseeb]

Hi

use concatenation between the two tables.

Regards

ASHFAQ

[maxgro]

T1:

LOAD * Inline [

F1, F2

a, 1

a, 2

b, 3

c, 4

];

T2:

NoConcatenate LOAD DISTINCT * Inline [

F1, F2

a, 1

b, 3

b, 3

c, 4

];

final:

NoConcatenate load * Resident T1;

load * Resident T2;

DROP Table T1, T2;

[tresesco]

Possibly you are struggling to get the distinct values from ONLY the second table. Since the load is causing auto concatenate, the DISTINCT is being applicabe for both the table here. To achieve the same, you have to avoid autoconcatenate and use force concatenate like below:

T1:

LOAD * Inline [

F1, F2

a, 1

a, 2

b, 3

c, 4

];

Concatenate

T2:

LOAD DISTINCT *, 1 as Dummy Inline [

F1, F2

a, 1

b, 3

b, 3

c, 4

];

[#5]

how  can i remove the Dummy column? Your solution is the closest. thanks

[ashfaq_haseeb]

Hi,

Use the below code.

T1:

LOAD * Inline [

F1, F2

a, 1

a, 2

b, 3

c, 4

];

Concatenate

T2:

LOAD DISTINCT *, 1 as Dummy Inline [

F1, F2

a, 1

b, 3

b, 3

c, 4

];

NoConcatenate

Table:

load F1,F2

Resident T1;

drop table T1

Hope it helped.

Regards

ASHFAQ

[#7]

Hi,

Try below script:

LOAD * INLINE [

    F1, F2

    a, 1

    a, 2

    b, 3

    c, 4

];



NoConcatenate



T2:

LOAD Distinct * INLINE [

    F1, F2

    a, 1

    b, 3

    b, 3

    c, 4

];



NoConcatenate

Final:

LOAD *
Resident T1;

Concatenate
load *
Resident T2;

DROP Table T1;
DROP Table T2;

Hope this will help u..!

[#8]

Wow this is great, thanks for the help.

Sorry if I ask from you to give me why this has to be done like that? I really like to understand rather than copying what it makes it solved.

[ashfaq_haseeb]

Hi,

Its good that you need to know.

Let me tell you.

T1:

LOAD * Inline [

F1, F2

a, 1

a, 2

b, 3

c, 4

];

Concatenate

T2:

LOAD DISTINCT *, 1 as Dummy Inline [

F1, F2

a, 1

b, 3

b, 3

c, 4

];

As tresco Explained. Since the load is causing auto concatenate, the DISTINCT is being applicable for both the table here. To achieve the same, you have to avoid auto concatenate and use force concatenate like below:

That is why he use force concatenation. but with 1 extra field.

As QlikView is in memory tool it loads everything in memory.

Then I use below code to eleminate the extra filed.

NoConcatenate

Table:

load F1,F2

Resident T1;

drop table T1

here NoConcatenate will not concatenate the above earlier loaded data in memory.

If we don't drop table above then there will be chances of data duplication.

This is how it works.

Regards

ASHFAQ

[tresesco]

You can drop field using 'drop field' command, however for this inline example, you ahve to load it extra one more time as residend and then drop like:

T1:

LOAD  * Inline [

F1, F2

a, 1

a, 2

b, 3

c, 4

];

Concatenate
T2:

LOAD Distinct *, 1 as Dummy Inline [

F1, F2

a, 1

b, 3

b, 3

c, 4

];
NoConcatenate
tab:
Load * Resident T1;
DROP Table T1;
DROP Field Dummy from tab;