Skip to main content
Question

Generated months using RANGE_APPEND but how to fill value using PQLExample: Lead time value changed in 2022-03 as 40 to 30 and after no change, the requirement is to generate months from 2022-04 to till now and show the 30 value to all forward m


Need to do in PQL, not in backend SQL

abhishek.chatu14
Level 7
Forum|alt.badge.img+3

Hi Kiran

 

The question is not very clear, the question gave me an impression that you wanted to use the RANGE_APPEND to generate months from 2022-04 till today but it seems like you have that covered....

 

What are you looking for actually ? Do you want to split your lead time for all the months generated ?

 

If yes shouldn't just adding RANGE_APPEND  as dimension and Lead Time in KPI give you the details ?


Thanks Abhishek, but in this case the Lead Time is non aggregate KPI, please find requirement in below screenshot, How to generate data as below in PQL not in SQL ?image


Forum|alt.badge.img+11

Hi Kiran,

Did you try the INTERPOLATE function?

Best,

Marcel


marcel.koolw12 wrote:

Hi Kiran,

Did you try the INTERPOLATE function?

Best,

Marcel

Thanks Marcel, Yes I tried with INTERPOLATE but it did not worked, may be because here I used RANGE_APPEND to generate months backward and forward(screenshot above). if you have any example with INTERPOLATE + RANGE_APPEND please share.


Forum|alt.badge.img+11
kiran.kanja14 wrote:

Thanks Marcel, Yes I tried with INTERPOLATE but it did not worked, may be because here I used RANGE_APPEND to generate months backward and forward(screenshot above). if you have any example with INTERPOLATE + RANGE_APPEND please share.

Hi Kiran,

I think you could do it like this:

Table with 1 record:

imageOLAP table with dimension:

TIMELINE_COLUMN ( MONTHS, FROM ( {d '2022-01-01' } ) , TO ( {d '2022-12-01' } ))

 

And KPI:

COALESCE(

  PU_LAST ( TIMELINE_TABLE ( "XX_TEST"."change_month" ) , "XX_TEST"."changed_to" ) ,

  LAG (PU_LAST ( TIMELINE_TABLE ( "XX_TEST"."change_month" ) , "XX_TEST"."changed_to" )) ,

  LEAD(PU_FIRST ( TIMELINE_TABLE ( "XX_TEST"."change_month" ) , "XX_TEST"."changed_from" ))

)

When there is a "changed_to" value for a certain month then that value will be taken. If there is no value for "changed_to" then it will take the "changed_to" from the previous month. If this is also not available it will take the first "changed_from" value from the next month.

 

This gives the following result:

image 

Best,

Marcel

 

 

 

 

 

 


kiran.kanja14 wrote:

Thanks Marcel, Yes I tried with INTERPOLATE but it did not worked, may be because here I used RANGE_APPEND to generate months backward and forward(screenshot above). if you have any example with INTERPOLATE + RANGE_APPEND please share.

Thanks Marcel, Unable to use TIMELINE functions along with direct fields from table, getting no common table found error also when used PU functions the values are not grouping correctly.

 


Forum|alt.badge.img+11

Hi Kiran,

What do you mean with "when used PU functions the values are not grouping correctly."?

 

I am not sure if there is a better way. but when I use:

PU_LAST ( TIMELINE_TABLE ( "XX_TEST"."change_month" ) , "XX_TEST"."material" )

and

PU_LAST ( TIMELINE_TABLE ( "XX_TEST"."change_month" ) , "XX_TEST"."plant" )

I get:

imageWhen you want the values for material and plant in all the rows you could use something similar as for the lead time:

for material

COALESCE(

  PU_LAST ( TIMELINE_TABLE ( "XX_TEST"."change_month" ) , "XX_TEST"."material" ) ,

  LAG (PU_LAST ( TIMELINE_TABLE ( "XX_TEST"."change_month" ) , "XX_TEST"."material" )) ,

  LEAD(PU_FIRST ( TIMELINE_TABLE ( "XX_TEST"."change_month" ) , "XX_TEST"."material" ))

)

for plant

COALESCE(

  PU_LAST ( TIMELINE_TABLE ( "XX_TEST"."change_month" ) , "XX_TEST"."plant" ) ,

  LAG (PU_LAST ( TIMELINE_TABLE ( "XX_TEST"."change_month" ) , "XX_TEST"."plant" )) ,

  LEAD(PU_FIRST ( TIMELINE_TABLE ( "XX_TEST"."change_month" ) , "XX_TEST"."plant" ))

)

The result:

imageBest,

Marcel


Reply