Issue with Concatenate

[Anonymous]

... ...

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

[Sokkorn]

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

[Anonymous]

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?

[Sokkorn]

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