Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Hi
use concatenation between the two tables.
Regards
ASHFAQ
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;
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
];
how can i remove the Dummy column? Your solution is the closest. thanks
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
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..!
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.
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
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;