Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Folks,
i got a question, my table does look like:
Produkt | Month | Value |
a | 1 | 2 |
a | 2 | 4 |
b | 1 | 20 |
b | 2 | 25 |
b | 3 | 30 |
as you see, i have dimenions: Month with 3 Month (1,2,3), but my product A does not have the month 3, my expected output is:
Produkt | Month | Value |
a | 1 | 2 |
a | 2 | 4 |
a | 3 | 0 |
b | 1 | 20 |
b | 2 | 25 |
b | 3 | 30 |
Does anybody have any idea how to generate a new / empty row?
Thanks a lot
Bek
try below one,
Tab:
Load
AutoNumber(Produkt&Month) as _KEY,
Produkt,
Month,
Value
Inline [
Produkt, Month, Value
a, 1, 2
a, 2, 4
b, 1, 20
b, 2, 25
b, 3, 30
];
Tab2:
Load Distinct Produkt as Prod Resident Tab;
Join
Load Distinct "Month" as MOn Resident Tab;
NoConcatenate
Tab3:
load *,
AutoNumber(Prod&MOn) as _KEY
Resident Tab2;
Drop Table Tab2;
Output:
@beck_bakytbek if you have a date field in your dataset then create a master calendar using that date field and derive and use derived month of master calendar in the table.
if there is no date field still we can generate a missing data, please refer the below one you will get it.
@Rockstar7 thanks a lot for your time and help. i try to implement it in my case and will let you know, when i achieve the expected result.
"if you have a date field in your dataset then create a master calendar using that date field and derive and use derived month of master calendar in the table" - can you show me the example. i implemented this issue, but it does not work.
thanks a lot
try below one,
Tab:
Load
AutoNumber(Produkt&Month) as _KEY,
Produkt,
Month,
Value
Inline [
Produkt, Month, Value
a, 1, 2
a, 2, 4
b, 1, 20
b, 2, 25
b, 3, 30
];
Tab2:
Load Distinct Produkt as Prod Resident Tab;
Join
Load Distinct "Month" as MOn Resident Tab;
NoConcatenate
Tab3:
load *,
AutoNumber(Prod&MOn) as _KEY
Resident Tab2;
Drop Table Tab2;
Output:
thank you very much for your help and your time, i will implement it in my issue and will let you know whether i achieve the expected output (mark your idea as solution)
Thanks a lot
Bek
@Rockstar7 Thank you very much for your help and your valuable time
Bek