Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

How to get similar outcome?

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.

13 Replies

Re: How to get similar outcome?

Hi

use concatenation between the two tables.

Regards

ASHFAQ

MVP
MVP

Re: Re: How to get similar outcome?

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;

MVP
MVP

Re: How to get similar outcome?

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

];

Not applicable

Re: How to get similar outcome?

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

Re: How to get similar outcome?

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

Not applicable

Re: How to get similar outcome?

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..!

Not applicable

Re: How to get similar outcome?

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.

Re: Re: How to get similar outcome?

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

MVP
MVP

Re: How to get similar outcome?

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;

Community Browser