Span open Table

[Necator]

Hello,

I got a very specific usecase, having following table:

Date	Country	Device	Count
01.06.2023	USA	iOS	5
01.06.2023	USA	Android	3
01.06.2023	UK	iOS	2
01.06.2023	UK	Android	7
02.06.2023	USA	Web	3

and so on, this table has multiple dimensions like Country, Device, Date etc. I wanna span this table open to get the amount of rows as the "Count" in the last column states. So 5 times the row with 01.06.23/USA/iOS. 3 times the second row etc. In this example I would come to a table with 20 rows.

I tried crosstable, but didn't quite manage it to get to work. 

Can anybody help ?

Best

[marcus_sommer · Accepted solution]

You may use an internal while-loop to create multiple rows of the raw-data records, for example with:

load *, recno() as RecNo, rowno() as RowNo, iterno() as IterNo from X
while iterno() <= Count;

[Necator]

Hi,

thanks I tried so, but i got like 100k records, this needs like 60mins reload, which is not nice. So i was hoping for a more performance oriented solution.

[marcus_sommer]

Usually is this approach very performant - especially compared with any alternatives - and I would expect by 100 k of records and an average duplication-rate of 10 and therefore 1 M of final records a run-time of about 1 - 2 minutes. If it runs a hour something is different and/or wrong.

[MarcoWedel]

what are you trying to achieve with those redundant rows?

[Necator]

I am building a variable forecast in the script, so the exisiting table is a resident load of the datamodel grouped by like 8 flexible dimensions, depending on the KPI. 

So what I am doing is calculating the forecast based on deimensions, metrics etc. and concatenate it to my datamodel in the future. In Visualization it would be great to just be doing something like sum(metric) and count(metric) not worrying about whether its the forecast or not. For sum metrics this is very easy,  because I can just concat those rows from the resident load. But for count metrics I need to span open the table and create disitinct keys out of the dimensions + iterNo. DOes this make any sense ?

[marcus_sommer · Accepted solution]

I think I would be rather doing a distinct extracting of the dimensional structures from your fact-table which should include all needed KPI's and then applying a cartesian join against the wanted future periods and then it could be concatenated to the fact-table. Additionally would be an extra source-information with values like 'current', 'plan', forecast' to differentiate between them per selections and/or with set analysis conditions.

With it all the needed dimensional structure exists to be shown within the UI and could be filled with the average of the last n periods and/or the planning-values and/or the values from the previous year and similar stuff + any extra variables for the users to play with to adjust the basic-forecast a bit more.

Of course such an approach is complex and needs a lot of efforts ... but if it should be dynamically selectable you couldn't much pre-calculate in the data-model - unless the above mentioned dimensional structure.