topic Issue with Concatenate in QlikView

Issue with Concatenate

Anonymous — Fri, 14 Oct 2011 02:04:41 GMT

... ...

all_LinkInsPack:

NoConcatenate

load distinct LinkInsPack as all_LinkInsPack

resident Sheet1_tmp4_1;

... ...

Sheet1_tmp6:
load distinct *
,HC &'-'& Ins &'-'& Pack as LinkHCInsPack
,Ins &'-'& Pack as LinkInsPack
Resident Sheet1_tmp5;

//Sheet1_tmp6 table includes 344592 records. Sheet1_tmp5 table has a field named IsCombined (0/1) .

drop table Sheet1_tmp5;

SalesTarget:

load Num#(Period) as Period

,LinkInsPack

,0 as SalesU

,TargetU_temp as TargetU

Resident Sheet1_tmp6

where Exists(all_LinkInsPack,LinkInsPack) and IsCombined='0';

//SalesTarget table includes 340728 records.

If I add script A as following

//script A

Concatenate

load Num#(Period) as Period

,LinkInsPack

,0 as SalesU

,TargetU_temp as TargetU

Resident Sheet1_tmp6

where Exists(all_LinkInsPack,LinkInsPack) and IsCombined='1';

//SalesTarget table will adds 3864 new records, and total have 344592 records

If I add script B instead of script A as following

//script B

Concatenate

load distinct Num#(Period) as Period

,LinkInsPack

,0 as SalesU

,TargetU_temp as TargetU

Resident Sheet1_tmp6

where Exists(all_LinkInsPack,LinkInsPack) and IsCombined='1';

//Now SalesTarget table will adds 1646 new records, but total have only 275950 records.

Why adding script B will decrease the amount of record in table SalesTarget?

The difference between script A and script B just a string "distinct".

Issue with Concatenate

Sokkorn — Fri, 14 Oct 2011 04:15:15 GMT

Hi,

Script B decrease record because of you use <DISTINCT> keyword. In this case table <Sheet1_tmp6> have duplicate record then if you use <distinct> keyword, you will retrive only 1 record from your source table.

Regards,

Sokkorn

Issue with Concatenate

Anonymous — Fri, 14 Oct 2011 07:28:15 GMT

I want to load records from table Sheet1_tmp6, which all rows when column IsCombined='1' and distinct rows when

column IsCombined='0'. How can I write the script?

Re: Issue with Concatenate

Sokkorn — Fri, 14 Oct 2011 08:36:30 GMT

Hi,

Try this

[tmp1]:
load  Num#(Period)        as Period
       ,LinkInsPack
       ,0                                      as SalesU
       ,TargetU_temp                   as TargetU
Resident Sheet1_tmp6
where Exists(all_LinkInsPack,LinkInsPack) and IsCombined='1';
Concatenate ([tmp1])
load  distinct Num#(Period)        as Period
       ,LinkInsPack
       ,0                                      as SalesU
       ,TargetU_temp                   as TargetU
Resident Sheet1_tmp6
where Exists(all_LinkInsPack,LinkInsPack) and IsCombined='0';

Let me know if this help you.

Regards,

Sokkorn