topic Concatenate Problem in QlikView

Concatenate Problem

chris1987 — Thu, 22 Sep 2016 12:50:16 GMT

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

Re: Concatenate Problem

sunny_talwar — Thu, 22 Sep 2016 17:06:34 GMT

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.

Re: Concatenate Problem

chris1987 — Fri, 23 Sep 2016 10:11:31 GMT

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

Re: Concatenate Problem

sunny_talwar — Fri, 23 Sep 2016 15:20:36 GMT

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