CODEWITHSUNDEEP
qliksenseqlik-expression
otwtm
otwtm

Reputation: 1999

Concatenate all values up to current date

Say we have a table of products and the year in which each product was sold. Using Qliksense, I would like to add a column that concatenates all products that were sold up to the respective year.

year product
2020 A
2021 B
2022 C

Desired outcome would be:

year product concatenate_products
2020 A A
2021 B A-B
2022 C A-B-C

See below for what I tried so far. It gave error "invalid expression".

Load
   *,
   if(RowNo()=1, 'A', Concat(distinct Peek(concatenate_products), product)) as concatenate_products
resident Table;

Upvotes: 0

Views: 171

Answers (1)

Stefan Stoychev
Stefan Stoychev

Reputation: 5012

Might be easier to create the new field in separate table and then join to the source table:

Load * Inline [
year, product
2020, A
2021, B
2022, C
];

NoConcatenate

// get list of only distinct year <-> product values
DistinctValues:
Load
 distinct
  year,
  product
Resident
  RawData
;


// calculate the combined product field
// and join back to the source table.
// the join will be performed on 2 fields: year and product
join (RawData)

Load
  *,
   if(RowNo() = 1, product, peek(concatenate_products) & '-' & product) as concatenate_products
Resident
  DistinctValues
;

Drop Table DistinctValues;

Result table:

result table

Upvotes: 1

Related Questions