Error : Field Names must be unique within table

[Anonymous]

Hi,

I am having the same problem but a bit differently. I wrote the following queries first to create the script:

Qualify*;

Unqualify booking_id, campaign_id, booking_line_id, booking_line_group_id, version;

[sm_campaign_header]:

LOAD

  [campaign_id],

  [campaign_code],

  [date_from] AS [ch.date_from],

  [date_to] AS [ch.date_to],

  [version] AS [ch.version],

  [status],

  [adv_code],

  [adv_name],

  [age_code],

  [age_name],

  [sales_team_name];

SQL SELECT `campaign_id`,

  `campaign_code`,

  `date_from`,

  `date_to`,

  `version`,

  `status`,

  `adv_code`,

  `adv_name`,

  `age_code`,

  `age_name`,

  `sales_team_name`

FROM `db_sm_report`.`sm_campaign_header`

where latest = 1;

[sm_booking_header]:

LOAD

  [booking_id],

  [booking_code],

  [campaign_id],

[version] AS [bh.version],

  [date_from] AS [bh.date_from],

  [date_to] AS [bh.date_to],

  [invoice_type_code];

SQL SELECT `booking_id`,

  `booking_code`,

  `campaign_id`,

`version`,

  `date_from`,

  `date_to`,

  `invoice_type_code`

FROM `db_sm_report`.`sm_booking_header`

where latest = 1;

[sm_booking_line]:

SQL SELECT `booking_line_id`,

  `booking_line_group_id`,

  `frame_extended_code`,

    `price_type`,

    price_gross,

    price_net,

    share_of_time,

    impressions

FROM `db_sm_report`.`sm_booking_line`;

[sm_booking_line_group]:

LOAD

  [booking_line_group_id],

  [booking_id],

  [date_week_from],

  [date_week_to],

    [campaign_dates],

    [version] as [blg.version];

SQL SELECT

booking_line_group_id,

booking_id,

date_week_from,

date_week_to,

version,

CONCAT(sm_booking_line_group.date_week_from, ' To ', sm_booking_line_group.date_week_to) as campaign_dates

from sm_booking_line_group;

When I use the above query my report brings revenue for all versions. Qlik is not able to distinguish the versions between the three table (sm_campaign_header, sm_booking_header, and sm_booking_line_group). Qlik is also not making joins between the tables based on the version.

If I use the following query I get, "Field Names must be unique within table" error.

select

c.campaign_id,

c.campaign_code,

c.status,

c.adv_code,

c.date_from,

c.date_to,

c.adv_name,

c.VERSION,

bh.booking_id,

bh.booking_code,

bh.campaign_id,

bh.date_from,

bh.date_to,

bh.latest,

bl.booking_line_id,

bl.booking_line_group_id,

bl.frame_extended_code,

bl.price_type,

blg.booking_line_group_id,

blg.booking_id,

blg.date_week_from,

blg.date_week_to,

bl.price_net

from sm_campaign_header c

JOIN sm_booking_header bh ON bh.campaign_id = c.campaign_id and c.version = bh.version

JOIN sm_booking_line_group blg ON blg.booking_id = bh.booking_id and blg.version = bh.version and blg.version = c.version

JOIN sm_booking_line bl on bl.booking_line_group_id = blg.booking_line_group_id

where bh.latest = 1

group by

c.campaign_id,

c.campaign_code,

c.status,

c.adv_code,

c.date_from,

c.date_to,

c.adv_name,

c.VERSION,

bh.booking_id,

bh.booking_code,

bh.campaign_id,

bh.VERSION,

bh.date_from,

bh.date_to,

bh.latest,

bl.booking_line_id,

bl.booking_line_group_id,

bl.frame_extended_code,

bl.price_type,

blg.booking_line_group_id,

blg.booking_id,

blg.VERSION,

blg.date_week_from,

blg.date_week_to;

Does anyone know any solution to this problem? Thanks.

[Anonymous]

Hi Syed,

Thanks for your post. I've branched your reply into its own thread so that other people can reply directly to you and you will be able to mark useful replies as Correct or Helpful.

Best regards,

Jeremy

[vishsaggi · Accepted solution]

Try your both scripts now and see:

Qualify*;

Unqualify booking_id, campaign_id, booking_line_id, booking_line_group_id, version;

[sm_campaign_header]:

LOAD

  [campaign_id],

  [campaign_code],

  [date_from] AS [ch.date_from],

  [date_to] AS [ch.date_to],

  [version] AS [version],

  [status],

  [adv_code],

  [adv_name],

  [age_code],

  [age_name],

  [sales_team_name];

SQL SELECT `campaign_id`,

  `campaign_code`,

  `date_from`,

  `date_to`,

  `version`,

  `status`,

  `adv_code`,

  `adv_name`,

  `age_code`,

  `age_name`,

  `sales_team_name`

FROM `db_sm_report`.`sm_campaign_header`

where latest = 1;

[sm_booking_header]:

LOAD

  [booking_id],

  [booking_code],

  [campaign_id],

  [version] AS [version],

  [date_from] AS [bh.date_from],

  [date_to] AS [bh.date_to],

  [invoice_type_code];

SQL

SELECT `booking_id`,

  `booking_code`,

  `campaign_id`,

  `version`,

  `date_from`,

  `date_to`,

  `invoice_type_code`

FROM `db_sm_report`.`sm_booking_header`

where latest = 1;

[sm_booking_line]:

SQL

SELECT `booking_line_id`,

  `booking_line_group_id`,

  `frame_extended_code`,

  `price_type`,

  price_gross,

  price_net,

  share_of_time,

  impressions

FROM `db_sm_report`.`sm_booking_line`;

[sm_booking_line_group]:

LOAD

  [booking_line_group_id],

  [booking_id],

  [date_week_from],

  [date_week_to],

  [campaign_dates],

  [version] as [version];

SQL SELECT

booking_line_group_id,

booking_id,

date_week_from,

date_week_to,

version,

CONCAT(sm_booking_line_group.date_week_from, ' To ', sm_booking_line_group.date_week_to) as campaign_dates

from sm_booking_line_group;

//When I use the above query my report brings revenue for all versions.

//Qlik is not able to distinguish the versions between the three table (sm_campaign_header, sm_booking_header, and //sm_booking_line_group). Qlik is also not making joins between the tables based on the version.

//If I use the following query I get, "Field Names must be unique within table" error.

TableName:

select

c.campaign_id,

c.campaign_code,

c.status,

c.adv_code,

c.date_from,

c.date_to,

c.adv_name,

c.VERSION,

bh.booking_id,

bh.booking_code,

bh.campaign_id,

bh.date_from,

bh.date_to,

bh.latest,

bl.booking_line_id,

bl.booking_line_group_id,

bl.frame_extended_code,

bl.price_type,

bl.price_net

blg.booking_line_group_id,

blg.booking_id,

blg.date_week_from,

blg.date_week_to

//, Here you had an extra comma which i removed.

from sm_campaign_header c

JOIN

    sm_booking_header bh

    ON

    bh.campaign_id = c.campaign_id and

    c.version      = bh.version

JOIN sm_booking_line_group blg

    ON

    blg.booking_id = bh.booking_id and

    blg.version    = bh.version    and

    blg.version    = c.version

JOIN sm_booking_line bl

    ON

    bl.booking_line_group_id = blg.booking_line_group_id

where bh.latest = 1

group by

c.campaign_id,

c.campaign_code,

c.status,

c.adv_code,

c.date_from,

c.date_to,

c.adv_name,

c.VERSION,

bh.booking_id,

bh.booking_code,

bh.campaign_id,

bh.VERSION,

bh.date_from,

bh.date_to,

bh.latest,

bl.booking_line_id,

bl.booking_line_group_id,

bl.frame_extended_code,

bl.price_type,

blg.booking_line_group_id,

blg.booking_id,

blg.VERSION,

blg.date_week_from,

blg.date_week_to;

[Anonymous]

It worked. Thank you very much.

[vishsaggi]

Did you fix your script?

[Anonymous]

Yes. Qlik is now loading data. Thank you again.

[vishsaggi]

Can you close the thread marking correct /helpful responses accordingly.

Thanks,
V.

[Anonymous]

Hi,

I marked your previous comment as the correct answer. Hopefully, this will close the thread. Please let me know if I need to take any additional action. Thanks.

[vishsaggi]

Nope. Thank you. All good.