How to assign same values to Nulls which is having primary key in Qlikview script while loading

[djaykumar]

Hi,

I am facing some issue with the following . I am having data set like

Customer_ID             Department

Customer1                  Finance

Customer1                     -              

Customer2                  HR

Customer2                    -      

Customer3                      -                

In the above data set    for Customer1  and Customer2 where the Department is empty I have to assign Finance and HR.

And in the same way for Customer3 I have to assign 'Not Assigned' in the Department field. 

This is one scenario and there are thousands of customers and around 30 Departments are there.

Please help me in this.

Thanks

Jay

[lironbaram]

hi 

you can add to your load script this expression: 

LOAD Customer_ID   ,

          Department,

          if(Customer_ID   =previous(Customer_ID  ) and isnull(Department), peek('DepartmentFull'),Department) as DepartmentFull

From 

[djaykumar]

Hi lironbaram,

Thanks for quick reply.

But Its not working . I am getting blanks(-) only

Please help me.

Thanks

Jay

[tresesco · Accepted solution]

Try this?

 if(Customer_ID   =previous(Customer_ID  ) and len(trim(Department))=0, peek('DepartmentFull'),if(len(trim(Department))=0,'Not Assigned', Department)) as DepartmentFull

[djaykumar]

Hi Tresesco,

Now its working,

Thanks,

Jay