Create new row in the table

[beck_bakytbek]

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

i want to create a new row with in a table:

Produkt	Month	Value
a	1	2
a	2	4
a	3	0
b	1	20
b	2	25
b	3	30

My product a has no month =3, so I wanted to ask if there is a way to add the missing month and column: Value with the value = 0.

Does anybode have any idea?

thanks a lot

[Andrei_Cusnir · Accepted solution]

Hello,

I am not 100% sure that your use case scenario is possible to be achieved in Qlik Sense load script. The issue here is a bit more complex than it looks and you need to take into consideration a lot of factors. For example here are some of the most important considerations:

1. What happens if the month value for particular product starts with other number than 1
2. What happens if there are multiple consecutive months missing in a row (So you have to insert multiple values)

Note: I mean that the above considerations need to be implemented in the script's logic

Because ideally you could just do the following:

1. Iterate through all the values in the dataset
2. Store the last read product and month value in a variable
3. Every time you read new values you have to check:
   1. If the product changed, store it as it is
   2. If the product didn't then:
      1. Check if the month is the same and store it as it is
      2. Check if the month is the expected one (month + 1) and store it as it is
      3. Check if the month is not the same and not the expected one, then:
         1. Calculate how many values need to be generated to cover the gap. For example if old month is 3 and new month is 7, you have to generate values 0 for months 4, 5 and 6

Note: Those are just some of the steps that you need to take into consideration

As you can see, the concept above, is a bit complicated and it will take you more time to find the right functions to achieve the use case scenario, if it is even possible. However, if somebody can't share with you a workaround here, then I would recommend to process the data (generate missing values) with a script (Python ideally) and then import the new dataset in Qlik Sense for analysis.

I hope that this information was helpful. In case I have misunderstood the use case scenario, please elaborate in details by providing additional information. However, if it has helped you resolve the issue, addressed your concerns or at least pointed you in the right direction, please mark it as Accepted Solution to give further visibility to other community members. 
 

[MendyS · Accepted solution]

Hi @beck_bakytbek 

if I got you,

try to load from your table to temp table only the Month field, 
after that load to table temp2 only the product field and after that make a cross join between two temp tables.

after that make the right join and this field - "  '0' as Value ", 
let me know if that resolves the problem