How to get same values in a row in qlilview

[djaykumar]

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

[Frank_Hartmann]

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,
];

[djaykumar]

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

[djaykumar]

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

[Frank_Hartmann]

you will have to use the  Field Department_New, not Department

see attached qvw

[sunny_talwar · Accepted solution]

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;

[Anil_Babu_Samineni]

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)

[djaykumar]

hankyou Sunny