Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
beck_bakytbek
Master
Master

Create new row in the table

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

 

Labels (1)
2 Solutions

Accepted Solutions
Andrei_Cusnir
Specialist
Specialist

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. 
 

Help users find answers! Don't forget to mark a solution that worked for you! 🙂

View solution in original post

MendyS
Creator II
Creator II

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

View solution in original post

2 Replies
Andrei_Cusnir
Specialist
Specialist

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. 
 

Help users find answers! Don't forget to mark a solution that worked for you! 🙂
MendyS
Creator II
Creator II

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