Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
nisha_rai
Creator II
Creator II

Generate data for those month which is not in table

Hi ,

Please help me to resolve the below issue:

I have a table which is comming monthly bases, but not continuouly bases. for example : we are getting data for Jan but till Aprile we are not getting any data , so for those moth we have to repeat Jan data, other senario is may be we are not getting any data till december.

in that case we have to repeat the whole data for full year.

Example:

MonthItemSub itemCountryAct SaleForecast Sale
1AXYZIndia030
1BXYZUK025
1APQRUSA010
1CQWEFrance040
1DRSTUAE020

MonthitemSub ItemCountryAct SaleForcast Sale
1AXYZIndia300
2BXYZUK100
3CQWEFrance250
4DRSTUAE200
5APQRUSA300
6BXYZUK450
7CPQRUSA400
8DQWEFrance150
9ARSTUAE25

0

In the Final result I want to repeat the first table data till 9th month

Output Reult:

MonthItemSub itemCountryAct SaleForecast Sale
1AXYZindia3030
1BXYZUK25
1APQRUSA10
1CQWEFrance40
1DRSTUAE20
2AXYZIndia30
2BXYZUK1025
2APQRUSA10
2CQWEFrance40
2DRSTUAE20

Like the above output our value will repeat till September.

Please help me to generate the data for moth which is not availble in table.

Thanks & Regards,

Nisha

24 Replies
ahaahaaha
Partner - Master
Partner - Master

Hi Nisha,


May be like at attached file.


Regards,

Andrey

MK_QSL
MVP
MVP

Your example and output table is not clear.. Can you provide proper output table?

nisha_rai
Creator II
Creator II
Author

Hi Andrey,

Thanks for your response,  if suppose after jan we receive the Forecast table in Aprile then my output will be

Jan , Feb , March data be the replica of jan data but after March we have April data then April to September we have the replica of April data, then how we can achive this sceario .

Regards,

Nisha

nisha_rai
Creator II
Creator II
Author

Hi Manish,

Below is my requirement:

Table1 Actual Data:( Table will time to time)

Item M01 M02 M03 M04 M05 M06 M07 M08 M09 M10 M11 M12

A      10   20 30 40 50 60 70 80 90 100 120 130

B       5   10 15 20 25 30 35 40 45 50 55 60

C       2 4 6 8 10 12 14 18 20 22 24 26

Table2  Forecast Data:( table not change regular bases, it will change based on the analysis)

Item Data Month

A 10  1

B 20  1

C 30  1

suppose Feb and march we are not getting any file, we are recive file in April

Table 2 Forecast Data:

Item  Data Month

A  25   4

B  35   4

C 45    4

Final Output For Table 2 Forecast table is:

Table2 Forecast Data:

Item Data Month

A 10  1

B  20 1

C 30 1

A 10 2

B 20 2

C  30 2

A  10  3

B   20  3

C   30   3

A    25  4

B 35    4

C 45  4

A   25   5

B   35   5

C   45   6

like that we have to repeat the data for rest of the month but in between month again if we receive the file  then the data will change for that month and rest of the month contain the same data from the latest month data.

Regards,

Nisha

PrashantSangle

You have to perform several steps to achieve it.

Logic is like

1: load your table

2: create Master Calender from min and max date of as per your table.

3: join calendar to your initial table

4: Take resident of new table i.e. output of your initial table join master calendar table then use Peek() to generate missing records.

Regards,

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
nisha_rai
Creator II
Creator II
Author

Hi Prashant,

I tried that logic but it's not giving me a values for those month which is not present in the Forecast table.

Regards,

PrashantSangle

After applying logic what value it is giving to you for missing months???

Can post your script??

Regards

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
PrashantSangle

After applying logic what value it is giving to you for missing months???

Can post your script??

Regards

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
nisha_rai
Creator II
Creator II
Author

if i am selecting feb,March then it's show zero, for Jan it's giving me 60.

Regards,