Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
This looks like similar to request
https://community.qlik.com/thread/178049
Are you both working on same requirement but created separate requests?
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;
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.
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;
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:
A:
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)&'/'¤cy) as Ccy,
if([Deal Id]=Previous([Deal Id]),Previous(price)&'/'&price) as Price,
if([Deal Id]=Previous([Deal Id]),Previous(notional)&'/'¬ional) 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
Thanks Jagan it's working ..
Great Work!..
Thanks,
Mahesh