How can I combine data from combined field to individual field?

[userpd_2021]

Hi All,

I have a table which contains 4 values Item Type, Month Year, Sales

Below is my current table . but now in the new table I want to have BOOK PEN & COVER. And add the values from book and cover to the individual column,

Item type	Item Flag	Month Year	Book Sales	Cover sales
Book	0|0	12.2024	10	0
Pen	1|0	12.2024	20	80
Cover	0|1	12.2024	0	0
Book and Cover	1|1	12.2024	10	10

OUTPUT:

Now Book and Cover for book sales was 10 as this is book sales, I added it to individual item BOOK similarly Book and Cover for book sales for Cover Sales was 10 hence added it to Cover item, how can I achieve this?

Item type	Item Flag	Month Year	Book Sales	Cover sales
Book	0|0	12.2024	20	0
Pen	1|0	12.2024	20	80
Cover	0|1	12.2024	0	10

[Ben_P]

I think the cleanest way to do this would be in the load script. You could create a new field duplicating field  'Item Type' as 'Item Type Group'. Then whenever you have an 'Item Type Group' value that is 'Book and Cover', you separate these out into a separate (non-concatenated) temporary table, and then append that temporary table back to your main table, setting 'Item Type' as 'Book' and setting Cover Sales=0, then do the same again but setting 'Item Type' as 'Cover' and Book Sales=0. Then delete your temporary table.

This will mean that you can create your first table visual using 'Item Type Group' as the dimension, and then create your second table visual using 'Item Type'. That way you don't lose any information on the combined 'Book and Cover' values.

Your resulting data model will look like this:

[Ciya]

Hi, 

This can be achieved using "Subfield" function. 

Use the below script line in your data load editor.

trim(subfield("Item type", 'and' )) as "Item type"

output: 

you can aggregate the data for Book and Cover sales 

Result:

[Clement15]

Hello,

There may be more optimized, but if you want to do it in the script there is this

Data:
Load 
*
Inline [
Item type, Item Flag, Month Year, Book Sales, Cover Sales
Book, 0|0, 12.2024, 10 ,0
Pen ,1|0 , 12.2024 ,20 ,80
Cover, 0|1 ,12.2024 ,0 ,0
Book and Cover, 1|1, 12.2024, 10 ,10
];

NoConcatenate

Data2:
LOAD
[Item type],
[Item Flag],
[Month Year],
[Book Sales],
[Cover Sales]
RESIDENT Data
WHERE NOT Match([Item type], 'Book and Cover');

Concatenate (Data2)
LOAD
'Book' AS [Item type],
[Month Year],
[Book Sales],
0 AS [Cover Sales]
RESIDENT Data
WHERE [Item type] = 'Book and Cover';

Concatenate (Data2)
LOAD
'Cover' AS [Item type],
[Month Year],
0 AS [Book Sales],
[Cover Sales]
RESIDENT Data
WHERE [Item type] = 'Book and Cover';


NoConcatenate

Final:
load
[Item type],
maxstring([Item Flag]) as [Item Flag],
[Month Year],
sum([Book Sales]) as [Book Sales],
sum([Cover Sales]) as [Cover Sales]
Resident Data2
Group by [Item type],
[Month Year];

DROP TABLE Data,Data2;