topic Re: Field representation on Group By function in QlikView

Field representation on Group By function

Thu, 27 Aug 2015 02:38:57 GMT

I have a scenario with below data,

Table:A

ID	Notation	Currency	Price
100	P	USD	1000
100	R	EUR	500
200	R	EUR	600
200	P	USD	800

Result required in the below format after applying group by on 'ID' under script,

ID	Notation	Currency	Price
100	P/R	USD/EUR	1000/500
200	R/P	EUR/USD	600/800

Please suggest me how to write under script in order to get the above result post applying the group by on 'ID' for table:A

Thanks in Advance.

Re: Field representation on Group By function

chaper — Thu, 27 Aug 2015 03:16:22 GMT

This looks like similar to request

https://community.qlik.com/thread/178049

Are you both working on same requirement but created separate requests?

Re: Field representation on Group By function

settu_periasamy — Thu, 27 Aug 2015 03:29:06 GMT

Hi,

Try the below script

A:
LOAD * INLINE [
ID, Notation, Currency, Price
    100, P, USD, 1000
    100, R, EUR, 500
    200, R, EUR, 600
    200, P, USD, 800
];
NoConcatenate
A1:
LOAD  ID, if(ID=Previous(ID),Previous(Notation)&'/'&Notation) as Notation,
   if(ID=Previous(ID),Previous(Currency)&'/'&Currency) as Currency,
   if(ID=Previous(ID),Previous(Price)&'/'&Price) as Price Resident A  Order by ID;
DROP Table A;
NoConcatenate
A2:
LOAD ID,Notation,Currency,Price Resident A1 where not IsNull(Notation);


DROP Table A1;

Re: Field representation on Group By function

jagan — Thu, 27 Aug 2015 03:32:51 GMT

Hi,

Try like this

Temp:

LOAD

INLINE [

ID, Notation, Currency, Price

100, P, USD, 1000

100, R, EUR, 500

200, R, EUR, 600

200, P, USD, 800];

Data:

NoConcatenate

LOAD

ID,

Concat(Notation, '/') AS Notation,

Concat(Currency, '/') AS Currency,

Concat(Price, '/') AS Price

RESIDENT Temp

GROUP BY ID;

DROP TABLE Temp;

Regards,

Jagan.

Re: Field representation on Group By function

jagan — Thu, 27 Aug 2015 03:35:29 GMT

Hi,

If you want the same order then use below script

Temp:

LOAD

RowNo() AS RowNum

INLINE [

ID, Notation, Currency, Price

100, P, USD, 1000

100, R, EUR, 500

200, R, EUR, 600

200, P, USD, 800];

Data:

NoConcatenate

LOAD

ID,

Concat(Notation, '/', RowNum) AS Notation,

Concat(Currency, '/', RowNum) AS Currency,

Concat(Price, '/', RowNum) AS Price

RESIDENT Temp

GROUP BY ID;

DROP TABLE Temp;

Re: Field representation on Group By function

Sat, 29 Aug 2015 23:09:26 GMT

Thanks Settu,

After applying logic as per your above suggestion I am almost achieved the result but resultant also includes duplicates rowsas below passion,

Script I used as blow for reference:

LOAD

deal_id AS [Deal Id] ,

pr ,

currency ,

price ,

notional ,

offset ,

ref_idx_desc

NoConcatenate

A1:

LOAD [Deal Id],if([Deal Id]=Previous([Deal Id]),Previous(pr)&'/'&pr) as [P/R],

if([Deal Id]=Previous([Deal Id]),Previous(currency)&'/'&currency) as Ccy,

if([Deal Id]=Previous([Deal Id]),Previous(price)&'/'&price) as Price,

if([Deal Id]=Previous([Deal Id]),Previous(notional)&'/'&notional) as Notional,

if([Deal Id]=Previous([Deal Id]),Previous(offset)&'/'&offset) as Offset,

if([Deal Id]=Previous([Deal Id]),Previous(ref_idx_desc)&'/'&ref_idx_desc) as [Ref Index Desc]

Resident Swap_Deal_New Order By [Deal Id];

Drop Table A;

NoConcatenate

A2:

LOAD

Distinct [Deal Id],[P/R],Ccy,Price,Notional,Offset,[Ref Index Desc] Resident A1 Where not IsNull(Notional);

DROP Table A1;

-----------------------------------------------------------------------------

Result:

I am expecting result for only one entry as P/R but not for P/P,R/R.

Please suggest the way forward and do needful

Thanks,

Mahesh

Re: Field representation on Group By function

Sun, 30 Aug 2015 19:56:41 GMT

Thanks Jagan it's working ..

Great Work!..

Thanks,

Mahesh