topic Re: How to get same values in a row in qlilview in QlikView

How to get same values in a row in qlilview

djaykumar — Sat, 16 Nov 2024 20:06:00 GMT

Hi All,

I am struggling with the below scenario in qlikview script

I have fields as follows

ProductId ItemID BillingType Department

A 2 FSD HR

A 2 MDS ---

C 3 FSD Finance

D 4 PTR ------

I have a requirement that, in the above data where ever ProductId and ItemId is same then have to assign same Department. and rest of the fields have to get same same Department as it is.

Output should be

ProductId ItemID BillingType Department

A 2 FSD HR

A 2 MDS HR

C 3 FSD Finance

D 4 PTR ------

Please help me in this

Thanks

Jay

Re: How to get same values in a row in qlilview

Frank_Hartmann — Wed, 11 Sep 2019 10:20:58 GMT

try this:

Load *, If(ProductId=Previous(ProductId) and ItemID=Previous(ItemID), peek(Department),Department) as Department_New; Load * Inline [ ProductId,ItemID,BillingType,Department A,2,FSD,HR A,2,MDS, C,3,FSD,Finance D,4,PTR, ];

Re: How to get same values in a row in qlilview

djaykumar — Wed, 11 Sep 2019 10:59:35 GMT

Hi Frank,

Thanks for Quick reply. But I am getting blank in department for productId A

Let be elaborate this

ProductID ItemId BillingType Department

A 2 FSD HR

A 3 PTR Finance

A 2 MDS --------

Output needed

ProductID ItemId BillingType Department

A 2 FSD HR

A 3 PTR Finance

A 2 MDS HR

Please help me in this

Thanks

Jay

Re: How to get same values in a row in qlilview

djaykumar — Wed, 11 Sep 2019 11:35:21 GMT

Hi Frank,

Thanks for quick reply. But I am getting blank in the Department for the ProductId A.

Please help me in this.

Thanks

Jay

Re: How to get same values in a row in qlilview

Frank_Hartmann — Wed, 11 Sep 2019 11:43:34 GMT

you will have to use the Field Department_New, not Department

see attached qvw

Re: How to get same values in a row in qlilview

sunny_talwar — Wed, 11 Sep 2019 11:47:01 GMT

This might be another option, but this assumes that you will only have one Department associated with a combination of ProductId and ItemID... if this is not true, there will be many to many join

T1: LOAD * INLINE [ ProductId, ItemID, BillingType, Department A, 2, FSD, HR A, 2, MDS C, 3, FSD, Finance D, 4, PTR ]; T2: LOAD ProductId, ItemID, BillingType Resident T1; Left Join (T2) LOAD Distinct ProductId, ItemID, Department Resident T1 Where Len(Trim(Department)) > 0; DROP Table T1;

Re: How to get same values in a row in qlilview

Anil_Babu_Samineni — Sun, 15 Sep 2019 10:14:54 GMT

Perhaps these ways?

Script:

T1:
LOAD * INLINE [
ProductId, ItemID, BillingType, Department
A, 2, FSD, HR
A, 2, MDS,
C, 3, FSD, Finance
D, 4, PTR,
];

Final:
NoConcatenate
LOAD ProductId, ItemID, BillingType, If(ItemID=Previous(ItemID), Peek(Department), Department) as Department1, Department Resident T1 Order By ProductId asc, ItemID asc;

DROP Table T1;

Design:

If(ItemID=Above(ItemID), Above(Department), Department)

Re: How to get same values in a row in qlilview

djaykumar — Wed, 18 Sep 2019 07:05:20 GMT

hankyou Sunny