Skip to main content
Announcements
Global Transformation Awards! Applications are now open. Submit Entry
cancel
Showing results for 
Search instead for 
Did you mean: 
chris1987
Creator
Creator

Concatenate Problem

Hi,

I'm trying to write daily figures to a table so I can create a history over time. I simple wantto add new records to a table if the "ID" doesn't exist.

The error I'm getting is "Field is unknown <ID>" which is obviously due to how I'm loading the data (generating "ID" on the fly) but I can't seem to figure out how to get around it. Any help would be really appreciated!

Cheers

Chris

Here's the code:

Tracker:

LOAD Database & AppName & FieldName & Date & Entity as [ID],

     Database,

     Entity,

     Date,

     AppName,

     FieldName,

     Value

FROM

(qvd);

Concatenate(Tracker)

LOAD

    'ukdoor' & 'UKDailyOrderScorecard' & 'OrderBank' & today() & 'UD' as [ID],

    'ukdoor' as Database,

    'UD' as Entity,

    today() as Date,

    'UKDailyOrderScorecard' as AppName,

    'OrderBank' as FieldName,

    sum([NetPrice(Ea)]*[Qty Open]) as [Value]

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq)

WHERE NOT Exists(ID);

1 Solution

Accepted Solutions
sunny_talwar

Try this:

Tracker:

LOAD Database & AppName & FieldName & Date(Date, 'DD-MMM-YYYY') & Entity as [ID],

    Database,

    Entity,

    Date,

    AppName,

    FieldName,

    Value

FROM

(qvd);

Concatenate(Tracker)

LOAD 'ukdoor' & 'UKDailyOrderScorecard' & 'OrderBank' & Date(Today(), 'DD-MMM-YYYY') & 'UD' as [ID],

    'ukdoor' as Database,

    'UD' as Entity,

    today() as Date,

    'UKDailyOrderScorecard' as AppName,

    'OrderBank' as FieldName,

    Sum([NetPrice(Ea)]*[Qty Open]) as [Value]

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq)

WHERE NOT Exists(ID, 'ukdoor' & 'UKDailyOrderScorecard' & 'OrderBank' & Date(Today(), 'DD-MMM-YYYY') & 'UD');

You need to make sure that Date format from the below table matches with the date format from the table above.

View solution in original post

3 Replies
sunny_talwar

Try this:

Tracker:

LOAD Database & AppName & FieldName & Date(Date, 'DD-MMM-YYYY') & Entity as [ID],

    Database,

    Entity,

    Date,

    AppName,

    FieldName,

    Value

FROM

(qvd);

Concatenate(Tracker)

LOAD 'ukdoor' & 'UKDailyOrderScorecard' & 'OrderBank' & Date(Today(), 'DD-MMM-YYYY') & 'UD' as [ID],

    'ukdoor' as Database,

    'UD' as Entity,

    today() as Date,

    'UKDailyOrderScorecard' as AppName,

    'OrderBank' as FieldName,

    Sum([NetPrice(Ea)]*[Qty Open]) as [Value]

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq)

WHERE NOT Exists(ID, 'ukdoor' & 'UKDailyOrderScorecard' & 'OrderBank' & Date(Today(), 'DD-MMM-YYYY') & 'UD');

You need to make sure that Date format from the below table matches with the date format from the table above.

chris1987
Creator
Creator
Author

Perfect, worked first time! Thanks for your help. Was the problem due to that I was trying to use the "ID" field before it has been set?

Cheers

Chris

sunny_talwar

Yes, your second table doesn't contain any field called ID and it is still in the making. So instead of using the new name, use the old name which is the concatenated field name you are creating.

HTH

Best,

Sunny