Concatenate Problem

[chris1987]

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);

[sunny_talwar · Accepted solution]

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]

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