topic Re: Error : Field Names must be unique within table in QlikView

Error : Field Names must be unique within table

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Error : Field Names must be unique within table

Anonymous — Wed, 23 May 2018 20:17:12 GMT

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

Re: Error : Field Names must be unique within table

vishsaggi — Wed, 23 May 2018 21:07:51 GMT

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

bh.campaign_id = c.campaign_id and

c.version = bh.version

JOIN sm_booking_line_group blg

blg.booking_id = bh.booking_id and

blg.version = bh.version and

blg.version = c.version

JOIN sm_booking_line bl

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;

Re: Error : Field Names must be unique within table

Anonymous — Wed, 23 May 2018 21:11:36 GMT

It worked. Thank you very much.

Re: Error : Field Names must be unique within table

vishsaggi — Wed, 23 May 2018 21:19:17 GMT

Did you fix your script?

Re: Error : Field Names must be unique within table

Anonymous — Wed, 23 May 2018 21:33:15 GMT

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

Re: Error : Field Names must be unique within table

vishsaggi — Thu, 24 May 2018 03:40:12 GMT

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

Thanks,
V.

Re: Error : Field Names must be unique within table

Anonymous — Thu, 24 May 2018 14:18:21 GMT

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.

Re: Error : Field Names must be unique within table

vishsaggi — Thu, 24 May 2018 16:30:13 GMT

Nope. Thank you. All good.