I got a table like this in Azure analytics with Kusto for the game I'm working on
datatable (ID_player:string, Timestamp:timespan, monster1:int, monster2:int, monster3:int)
[
"aaa", "12:00:00", 1,2,3
,"aaa", "12:10:00", 4,7,0
,"bbb", "12:30:00", 0,2,1
]
Basically, I need to switch to a format like this one
ID_Player Timespamp Monster Quantity
aaa 12:00:00 Monster1 1
aaa 12:00:00 Monster2 2
aaa 12:00:00 Monster3 3
aaa 12:10:00 Monster1 4
aaa 12:10:00 Monster2 7
aaa 12:10:00 Monster3 0
bbb 12:30:00 Monster1 0
bbb 12:30:00 monster2 2
bbb 12:30:00 Monster3 1
ny idea? I tought to use a series of CASE WHEN But I don't think it's the right solution. Thanks in advance!!!
Here are 3 more straightforward solutions, based on mv-expand & pack_dictionary() / pack_array()
pack_dictionary()
+ mv-expand kind = array
datatable (ID_player:string, Timestamp:timespan, monster1:int, monster2:int, monster3:int)
[
"aaa", "12:00:00", 1,2,3
,"aaa", "12:10:00", 4,7,0
,"bbb", "12:30:00", 0,2,1
]
| mv-expand kind = array monster = pack_dictionary("Monster1", monster1, "Monster2", monster2, "Monster3", monster3)
| extend Monster = tostring(monster[0]), Quantity = toint(monster[1])
| project-away monster*
pack_array()
x2 + mv-expand
datatable (ID_player:string, Timestamp:timespan, monster1:int, monster2:int, monster3:int)
[
"aaa", "12:00:00", 1,2,3
,"aaa", "12:10:00", 4,7,0
,"bbb", "12:30:00", 0,2,1
]
| mv-expand Monster = pack_array("Monster1", "Monster2", "Monster3") to typeof(string)
,Quantity = pack_array( monster1, monster2, monster3 ) to typeof(int)
| project-away monster*
pack_array()
+ mv-expand with_itemindex
datatable (ID_player:string, Timestamp:timespan, monster1:int, monster2:int, monster3:int)
[
"aaa", "12:00:00", 1,2,3
,"aaa", "12:10:00", 4,7,0
,"bbb", "12:30:00", 0,2,1
]
| mv-expand with_itemindex = i Quantity = pack_array(monster1, monster2, monster3) to typeof(int)
| extend Monster = strcat("Monster", tostring(i + 1))
| project ID_player, Timestamp, Monster, Quantity