Concat fields in load statement

Question

I have a simple load statement:

LOAD FIELDA,
     FIELDB,
     FIELDC,
     FIELDD,
     FIELDE
FROM
[C:\Users\XXX\data.QVD]
(qvd);

This loads all the data fine.

If i add an expression into a straight table it gives me the concatenated field:

 concat(FIELDA&'.'&FIELDB&'.'&FIELDC&'.'&IF(Isnull(FIELDD), '0', FIELDD)&'.'&IF(Isnull(FIELDE), '0', FIELDE)) as MERGED

This works, however if i try and add a concatenated field from the load statement i get an error:

LOAD FIELDA,
     FIELDB,
     FIELDC,
     FIELDD,
     FIELDE,
     concat(FIELDA&'.'&FIELDB&'.'&FIELDC&'.'&IF(Isnull(FIELDD), '0', FIELDD)&'.'&IF(Isnull(FIELDE), '0', FIELDE)) as MERGED
FROM
[C:\Users\XXX\data.QVD]
(qvd);

Invalid expression

Matt · Accepted Answer · 2016-05-12 07:31:25Z

3

The concat function is an aggregation function so it wants a group by to know what to do, hence the Invalid expression error. In the front end the dimension you choose for the chart serves that purpose.

If you just want to concat the fields line for line just remove the concat function.

FIELDA&'.'&FIELDB&'.'&FIELDC&'.'&IF(Isnull(FIELDD), '0', FIELDD)&'.'&IF(Isnull(FIELDE), '0', FIELDE) as MERGED

The actual use of the concat function would be something like this, run it and see the difference between MERGE and MERGE2;

A:

LOAD * INLINE [
    FieldA, FieldB,FieldC
    1, 2,1
    1, 0,1
    2, 9,1
    2, 4,1
];

B:

load FieldA,     
     concat(FieldB,'|') as MERGE     
Resident A
group by FieldA;

C:

load FieldA,
     FieldB,
     FieldC,
     FieldA&'.'&FieldB&'.'&FieldC as MERGE2
resident A;

drop table A;

edited May 12, 2016 at 7:31

Matt

15.1k28 gold badges109 silver badges161 bronze badges

answered Apr 23, 2015 at 15:46

The Budac

1,6151 gold badge9 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Concat fields in load statement

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related