Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor
Contributor

Error : Field Names must be unique within table

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.

1 Solution

Accepted Solutions
Highlighted
Champion III
Champion III

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;

View solution in original post

8 Replies
Highlighted
Anonymous
Not applicable

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

Highlighted
Champion III
Champion III

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;

View solution in original post

Highlighted
Contributor
Contributor

It worked. Thank you very much.

Highlighted
Champion III
Champion III

Did you fix your script?

Highlighted
Contributor
Contributor

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

Highlighted
Champion III
Champion III

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

Thanks,
V.

Highlighted
Contributor
Contributor

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.

Highlighted
Champion III
Champion III

Nope. Thank you. All good.