Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

djaykumar
New Contributor

How to get same values in a row in qlilview

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

1 Solution

Accepted Solutions

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

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;
7 Replies
Frank_Hartmann
Honored Contributor II

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

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
New Contributor

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

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
New Contributor

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

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
Honored Contributor II

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

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

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

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)

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
djaykumar
New Contributor

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

hankyou Sunny