topic Re: Generate data for those month which is not in table in QlikView

Generate data for those month which is not in table

nisha_rai — Wed, 25 Nov 2020 16:16:04 GMT

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:

Month	Item	Sub item	Country	Forecast Sale
1	A	XYZ	India	30
1	B	XYZ	UK	25
1	A	PQR	USA	10
1	C	QWE	France	40
1	D	RST	UAE	20

Month	item	Sub Item	Country	Act Sale
1	A	XYZ	India	30
2	B	XYZ	UK	10
3	C	QWE	France	25
4	D	RST	UAE	20
5	A	PQR	USA	30
6	B	XYZ	UK	45
7	C	PQR	USA	40
8	D	QWE	France	15
9	A	RST	UAE	25

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

Output Reult:

Month	Item	Sub item	Country	Act Sale	Forecast Sale
1	A	XYZ	india	30	30
1	B	XYZ	UK		25
1	A	PQR	USA		10
1	C	QWE	France		40
1	D	RST	UAE		20
2	A	XYZ	India		30
2	B	XYZ	UK	10	25
2	A	PQR	USA		10
2	C	QWE	France		40
2	D	RST	UAE		20

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

Re: Generate data for those month which is not in table

ahaahaaha — Mon, 03 Jul 2017 14:20:37 GMT

Hi Nisha,

May be like at attached file.

Regards,

Andrey

Re: Generate data for those month which is not in table

MK_QSL — Mon, 03 Jul 2017 14:21:13 GMT

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

Re: Generate data for those month which is not in table

nisha_rai — Tue, 04 Jul 2017 06:25:01 GMT

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

Re: Generate data for those month which is not in table

nisha_rai — Tue, 04 Jul 2017 06:57:27 GMT

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

Re: Generate data for those month which is not in table

PrashantSangle — Tue, 04 Jul 2017 07:07:02 GMT

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,

Re: Generate data for those month which is not in table

nisha_rai — Tue, 04 Jul 2017 07:15:52 GMT

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,

Re: Generate data for those month which is not in table

PrashantSangle — Tue, 04 Jul 2017 07:18:29 GMT

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

Can post your script??

Regards

Re: Generate data for those month which is not in table

PrashantSangle — Tue, 04 Jul 2017 07:18:33 GMT

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

Can post your script??

Regards

Re: Generate data for those month which is not in table

nisha_rai — Tue, 04 Jul 2017 07:22:22 GMT

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

Regards,

Re: Generate data for those month which is not in table

nisha_rai — Tue, 04 Jul 2017 10:05:14 GMT

TempTable_FTE:
Load date(MakeDate('2017',PlanMthNum),'DD\MM\YYYY') as FTE_For_Date,* from table1.qvd
(qvd);

MinMaxDate:
Load Min(FTE_For_Date) as MinDate_FTE,
Max(FTE_For_Date) as MaxDate_FTE
Resident TempTable_FTE;
Let vMinDate_FTE =peek('MinDate_FTE',-1,'MinMaxDate');
Let vMaxDate_FTE = peek('MaxDate_FTE',0,'MinMaxDate_FTE');

join(TempTable_FTE)
Load Date(recno()+$(vMinDate_FTE)) as FTE_For_Date AutoGenerate $(vMaxDate_FTE) - $(vMinDate_FTE);

Forecast_FTE:
NoConcatenate
Load *,
if(IsNull(M12),Peek(M12),M12) as FTE_Forcast1
Resident TempTable_FTE
order by FTE_For_Date;

drop table MinMaxDate, TempTable_FTE;

Re: Generate data for those month which is not in table

d_prashanthredd — Tue, 04 Jul 2017 10:39:21 GMT

Hi Nisha,

Try this.. @ How to populate a sparsely populated field

I hope, it will help you.

Re: Generate data for those month which is not in table

nisha_rai — Tue, 04 Jul 2017 10:48:19 GMT

Tried the same but i am not getting result

Regards,

Re: Generate data for those month which is not in table

PrashantSangle — Tue, 04 Jul 2017 12:00:58 GMT

Hi,

Sorry for late reply

in peek() try below

if(IsNull(M12),Peek(FTE_Forcast1),M12) as FTE_Forcast1

Regards,

Re: Generate data for those month which is not in table

nisha_rai — Tue, 04 Jul 2017 12:35:37 GMT

Thanks Prashant,

It's repeating the data but for only one product not for all product.

Regards,

Re: Generate data for those month which is not in table

MK_QSL — Tue, 04 Jul 2017 13:06:24 GMT

Temp:
CrossTable(Month,Value)
Load * Inline
[
Item, 1, 2,  3, 4, 5, 6, 7, 8, 9, 10, 11, 12
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
];


NoConcatenate
T1:
Load Item, Num#(Month,'#0') as Month, Value Resident Temp;


Drop Table Temp;




TempT2:
Load Item, Data, Month, Item&'|'&Month as Key Inline
[
Item, Data, Month
A, 10,  1
B, 20,  1
C, 30,  1
A,  25,  4
B,  35,  4
C, 45,  4
A,  125, 9
B,  130, 9
C, 140, 9
];


Temp:
Load Distinct Item Resident T1;
Join
Load Distinct Month Resident T1;


Concatenate(TempT2)
Load * Where Not Exists (Key);
Load 
Item,
Month,
Item&'|'&Month as Key
Resident Temp; 


Drop Table Temp;
Drop Field Key;


NoConcatenate
T2:
Load 
Item, 
Month,
If(Item = Peek(Item) and IsNull(Data), Peek(Data), RangeSum(Data)) as Data  
Resident TempT2 Order By Item, Month;


Drop Table TempT2;

Re: Generate data for those month which is not in table

d_prashanthredd — Tue, 04 Jul 2017 13:29:49 GMT

Hi Nisha,

Yes, we are getting data only for one item when we tried so. Below is working fine.. Have a look at it and let me know if any problem.

MonthsData:

load * Inline [

Item,Data,Month

A,10,1

B,20,1

C,30,1

];

load * Inline [

Item,Data,Month

A,25,4

B,35,4

C,45,4

];

MinMaxMonth:

Load Min(Month) as MinMonth, Max(Month) as MaxMonth resident MonthsData;

Let vMinMonth = Peek('MinMonth',-1,'MinMaxMonth') - 1;

Let vMaxMonth = Peek('MaxMonth',-1,'MinMaxMonth') ;

join (MonthsData)

MissingData:

Load Item, ($(vMinMonth) + IterNo()) as Month Resident MonthsData

While IterNo() <= ($(vMaxMonth)-1) - ($(vMinMonth)-1);

Rates:

NoConcatenate Load Item, Month,

If( IsNull( Data ), Peek( Data ), Data ) as Data

Resident MonthsData

Order By Item, Month ;

drop table MinMaxMonth, MonthsData;

Output:

Re: Generate data for those month which is not in table

nisha_rai — Tue, 04 Jul 2017 13:52:38 GMT

Hi Prashanth,

It's not giving me the value when more columns are added in the table.

Re: Generate data for those month which is not in table

nisha_rai — Tue, 04 Jul 2017 14:11:31 GMT

Hi Manish,

This also not giving me the correct value, even it's not populated the month which are missing.

Re: Generate data for those month which is not in table

MK_QSL — Tue, 04 Jul 2017 14:18:25 GMT

Can you give an example from my above script, which item is not giving output for which month?