Solved: Re: Search field value in another field (occurance... - Qlik Community

OmarBenSalem · ‎2017-12-14

Hi all ( stalwar1‌ , swuehl‌

I have 2 table:

1) table1 have a field called Key as follow:

Key :

if(len(Trim(Product_Code))=0 and Len(Trim(Currency))=0,CDR_Standardized,

if(len(Trim(Product_Code))=0 and Len(Trim(Currency))<>0 ,CDR_Standardized&'|'&Currency,

if(len(Trim(Product_Code))<>0 and Len(Trim(Currency))=0 ,CDR_Standardized&'|'&Product_Code,

(CDR_Standardized&'|'&Product_Code&'|'&Currency)))) as Key

It has values such as :

70648|USD that's associated to an ID=2 for example !

Now the Key in the second table contains values like this:

XXX|70648|USD|02

AA|70648|USD|05

DD|70648|USD

70648|USD|1919|EST

so; the ID=2 is obviously not associated with :

XXX|70648|USD|02

AA|70648|USD|05

DD|70648|USD

70648|USD|1919|EST

The question is: how to do to associate these values since Key from the first table exists in the second?

Thanks a ton !

OmarBenSalem · ‎2017-12-20

stalwar1‌ !

I made it woooooooooooooooooooooooooork

Thresholds:

load * ,if(len(Trim(THR.Product_Code))=0 and Len(Trim(THR.Currency))=0,'|'&THR.CDR_Standardized&'|',

if(len(Trim(THR.Product_Code))=0 and Len(Trim(THR.Currency))<>0 ,'|'&THR.CDR_Standardized&'|'&THR.Currency&'|',

if(len(Trim(THR.Product_Code))<>0 and Len(Trim(THR.Currency))=0 ,'|'&THR.CDR_Standardized&'|'&THR.Product_Code&'|',

('|'&THR.CDR_Standardized&'|'&THR.Product_Code&'|'&THR.Currency&'|')))) as Key ;

LOAD

[ThresholdUniqueID] AS [ThresholdUniqueID],

[Threshold_ID] AS [Threshold_ID],

upper([Trading_Desk]) AS [THR.Trading_Desk],

upper([CDR_Standardized]) AS [THR.CDR_Standardized],

upper([Product_Code]) AS [THR.Product_Code],

upper([Currency]) AS [THR.Currency],

[Daily_Variation] AS [Daily_Variation],

[Monthly_Variation] AS [Monthly_Variation],

[Yearly_Variation] AS [Yearly_Variation],

[Aggregation_Type] AS [Aggregation_Type]

RESIDENT RestConnectorMasterTable

Where Date(Today(1)) >=Date([Valid_From]) and Date(Today(1))<=Date([Valid_To]) and Capitalize([Active])='True'

;

DROP TABLE RestConnectorMasterTable;

Thresholds_keywoords0:

NoConcatenate

LOAD ThresholdUniqueID,trim(text(subfield(Key,'|'))) as kw

resident Thresholds;

Thresholds_keywoords:

NoConcatenate

LOAD ThresholdUniqueID,kw

resident Thresholds_keywoords0

where isnull(kw)=0 and kw<>'' and trim(kw)<>'';

drop table Thresholds_keywoords0;

left join(Thresholds)

LOAD ThresholdUniqueID,count(DISTINCT kw) as keycount,count(DISTINCT kw) as countmatchthr

resident Thresholds_keywoords

//where isnull(kw)=0 and trim(kw)<>''

group by ThresholdUniqueID;

//******************************** Notionals table **********************************************

Not:

LOaD

AsOf_Date,

Tree_ID,

Entity,

CDR,

Book,

Portfolio,

Branch,

Folder,

Product_ID,

upper([Trading_Desk]) AS [Trading_Desk],

upper([CDR_Standardized]) AS [CDR_Standardized],

upper([Product_Code]) AS [Product_Code],

upper([Currency]) AS [Currency],

Current_Notional_Net,

Current_Notional_Gross

,

Extraction_Date

FROM [lib://Medium Data/notional.csv]

(txt, codepage is 28591, embedded labels, delimiter is ',', msq) where len(Trim(CDR_Standardized))<>0 ;

Join(Not)

LOAD

AsOf_Date

,

Base_Currency as Currency,

Pivot_Currency,

FX_Rate

FROM [lib://Medium Data/fxRate.csv]

(txt, codepage is 28591, embedded labels, delimiter is ',', msq)

;

Notionals:

NoConcatenate

load

rowno() as NotId,

*,

if(len(Trim(Product_Code))=0 and Len(Trim(Currency))=0,'|'&CDR_Standardized&'|',

if(len(Trim(Product_Code))=0 and Len(Trim(Currency))<>0 ,'|'&CDR_Standardized&'|'&Currency&'|',

if(len(Trim(Product_Code))<>0 and Len(Trim(Currency))=0 ,'|'&CDR_Standardized&'|'&Product_Code&'|',

('|'&CDR_Standardized&'|'&Product_Code&'|'&Currency&'|')))) as key

Resident Not;

Drop Table Not;

left join(Notionals)

LOAD ThresholdUniqueID,// as idfk,

text(kw) as kw2//,countmatchthr

resident Thresholds_keywoords;

table3_prefilter:

NoConcatenate

LOAD *,

if(index('|' & key,'|'&kw2&'|')>0,1,0) as ismatch

resident Notionals;

mainfact0:

NoConcatenate

LOAD distinct NotId,ThresholdUniqueID,kw2,1 as ismatch Resident table3_prefilter where ismatch=1;

drop table table3_prefilter;

left join(mainfact0)

LOAD ThresholdUniqueID,countmatchthr

Resident Thresholds;

mainfact1:

LOAD NotId,ThresholdUniqueID,countmatchthr,count(ismatch) as countmatchnot

resident mainfact0

group by NotId,ThresholdUniqueID,countmatchthr;

drop table mainfact0;

mainfact:

NoConcatenate

LOAD NotId,ThresholdUniqueID resident mainfact1 where countmatchthr=countmatchnot;

drop table mainfact1;

drop fields ThresholdUniqueID from Notionals;

View solution in original post

sunny_talwar · ‎2017-12-14

May be you can use MapSubstring - script function ‒ QlikView to extract the right value from the second table?

OmarBenSalem · ‎2017-12-14

Yes , I already thought about that; but I don't think I have any staminia left to translate it to my needs; this is why I'm in a need of your kindness !

Let me do a simple example of what I have / want to do:

table 1:

load * inline [

ID, Key, Value1,Value2

2, 70648|USD ,Valuex, valuey

];

table2:

load* inline [

key, otherField

XXX|70648|USD|02, 300

AA|70648|USD|05, 500

DD|70648|USD, 200

70648|USD|1919|EST, 150

];

Now, when I FILTER WITH ID=2 FROM TABLE1. I SHOULD HAVE

sum(OtherField) =300+500+200+150

since ID=2 is associated with the key 70648|USD

that exists in

XXX|70648|USD|02

AA|70648|USD|05

DD|70648|USD

70648|USD|1919|EST

Hope u can help me with that sunny..

sunny_talwar · ‎2017-12-14

This

table1:

LOAD * INLINE [

ID, Key, Value1, Value2

2, 70648|USD, Valuex, valuey

];

Mapping:

Mapping

LOAD DISTINCT Key,

'\' & Key & '/'

Resident table1;

table2:

LOAD *,

TextBetween(MapSubString('Mapping', key), '\', '/') as Key;

LOAD * INLINE [

key, otherField

XXX|70648|USD|02, 300

AA|70648|USD|05, 500

DD|70648|USD, 200

70648|USD|1919|EST, 150

];

OmarBenSalem · ‎2017-12-14

You're a life savior Sun !

OmarBenSalem · ‎2017-12-18

Hi stalwar1‌, with the way things are

for example:

FxFA | FX | EUR is associated with FxFA | FX | EUR and FxFA | FX | EUR | 02

BUT

when in one hand I have:

FxFA | EUR

and in the other hand :

FxFA | FX | EUR

and

FxFA | FXSWAP | EUR

these does not get associated.. even though, the FxFA | EUR does exist in both of the second values in table 2 (but the order is not respected..do u see it?)

Is there a way to associate this :

FxFA | EUR

to :

FxFA | FX | EUR

and

FxFA | FXSWAP | EUR

Thanks a ton Sun !

OmarBenSalem · ‎2017-12-18

I thought about using subfield to seperate FxFA | EUR and see if both (at the same time) exists in

FxFA | FX | EUR

and

FxFA | FXSWAP | EUR;

if yes (both exist): associate :

FxFA | EUR to

FxFA | FX | EUR

and

FxFA | FXSWAP | EUR

if not: do not associate..

Easy said than done..

OmarBenSalem · ‎2017-12-18

This is how I've done:

table1:

load *, SubField(Key,'|',1) as NewKey,SubField(Key,'|',2) as NewKey2;

LOAD * INLINE [

ID, Key, Value1, Value2

2, USD|70648, Valuex, valuey

];

Mapping:

Mapping

LOAD DISTINCT NewKey,

'\' & NewKey & '/'

Resident table1;

Mapping2:

Mapping

LOAD DISTINCT NewKey2,

'\' & NewKey2 & '/'

Resident table1;

table2:

LOAD *,

TextBetween(MapSubString('Mapping', key), '\', '/') as NewKey,

TextBetween(MapSubString('Mapping2', key), '\', '/') as NewKey2;

LOAD * INLINE [

key, otherField

XXX|70648|02, 300

AA|70648|05|USD, 500

DD|70648|USD, 200

70648|USD|1919|EST, 150

];

result: (right one) :

prblem 1 :

It's static; what if Key was composed of more than 2 values...

problem 2 :

Synthetic keys generation

How could/should I optimize this?

Thanks ! stalwar1‌

OmarBenSalem · ‎2017-12-18

This works only where there are 3 values constructing the first key in table1:

table:

LOAD * INLINE [

ID, Key, Value1, Value2

3, 70648,50,20

2, USD|70648, Valuex, valuey

4, USD|70648|xx, Valuex, valuey //works for this one !

1, fxFA | EUR, 20,30

];

NoConcatenate

table1:

Load *, trim(SubField(Key,'|',1)) as NewKey, trim(SubField(Key,'|',2)) as NewKey2, trim(SubField(Key,'|',3)) as NewKey3 Resident table where len(KeepChar(Key,'|'))<>0 ;

Drop Table table;

Mapping:

Mapping

LOAD DISTINCT NewKey,

'\' & NewKey & '/'

Resident table1;

Mapping2:

Mapping

LOAD DISTINCT NewKey2,

'\' & NewKey2 & '/'

Resident table1;

Mapping3:

Mapping

LOAD DISTINCT NewKey3,

'\' & NewKey3 & '/'

Resident table1;

Join (table1) // to eliminate the synthetic key; maybe it's doing sthing wrong..

table2:

LOAD *,

TextBetween(MapSubString('Mapping', key), '\', '/') as NewKey,

TextBetween(MapSubString('Mapping3', key), '\', '/') as NewKey3,

TextBetween(MapSubString('Mapping2', key), '\', '/') as NewKey2;

LOAD * INLINE [

key, otherField

XXX|70648|02, 300

fxFA | EUR, 500

EUR|fxFA|200,200

300|fxFA,10

USD|70648|xx,50

xx|300|USD|70648,100

EUR|500|fxFA,300

AA|70648|05|USD, 500

DD|70648|USD, 200

70648|USD|1919|EST, 150

];

final:

NoConcatenate Load * Resident table1 where NewKey&NewKey2&NewKey3<>NewKey and NewKey&NewKey2&NewKey3<>NewKey2 and NewKey&NewKey2&NewKey3<>NewKey3

;

Drop Table table1;

NOT CORRECT

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

This works only where there are 2 values constructing the first key in table1:

table:

LOAD * INLINE [

ID, Key, Value1, Value2

3, 70648,50,20

2, USD|70648, Valuex, valuey //works for this one !

4, USD|70648|xx, Valuex, valuey

1, fxFA | EUR, 20,30 //works for this one !

];

NoConcatenate

table1:

Load *, trim(SubField(Key,'|',1)) as NewKey, trim(SubField(Key,'|',2)) as NewKey2 Resident table where len(KeepChar(Key,'|'))<>0 ;

Drop Table table;

Mapping:

Mapping

LOAD DISTINCT NewKey,

'\' & NewKey & '/'

Resident table1;

Mapping2:

Mapping

LOAD DISTINCT NewKey2,

'\' & NewKey2 & '/'

Resident table1;

Join (table1) // to eliminate the synthetic key; maybe it's doing sthing wrong..

table2:

LOAD *,

TextBetween(MapSubString('Mapping', key), '\', '/') as NewKey,

TextBetween(MapSubString('Mapping2', key), '\', '/') as NewKey2;

LOAD * INLINE [

key, otherField

XXX|70648|02, 300

fxFA | EUR, 500

EUR|fxFA|200,200

300|fxFA,10

USD|70648|xx,50

xx|300|USD|70648,100

EUR|500|fxFA,300

AA|70648|05|USD, 500

DD|70648|USD, 200

70648|USD|1919|EST, 150

];

final:

NoConcatenate Load * Resident table1 where NewKey&NewKey2<>NewKey and NewKey&NewKey2<>NewKey2

;

Drop Table table1;

Not correct :

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

and finally,

This works only where there are only one value constructing the first key in table1:

table:

LOAD * INLINE [

ID, Key, Value1, Value2

3, 70648,50,20 //works for this one !

2, USD|70648, Valuex, valuey

4, USD|70648|xx, Valuex, valuey

1, fxFA | EUR, 20,30

];

NoConcatenate

table1:

Load *, Key as NewKey,Key as NewKey2 Resident table where len(KeepChar(Key,'|'))=0 ;

Drop Table table;

Mapping:

Mapping

LOAD DISTINCT NewKey,

'\' & NewKey & '/'

Resident table1;

Mapping2:

Mapping

LOAD DISTINCT NewKey2,

'\' & NewKey2 & '/'

Resident table1;

Join (table1) // to eliminate the synthetic key; maybe it's doing sthing wrong..

table2:

LOAD *,

TextBetween(MapSubString('Mapping', key), '\', '/') as NewKey,

TextBetween(MapSubString('Mapping2', key), '\', '/') as NewKey2;

LOAD * INLINE [

key, otherField

XXX|70648|02, 300

fxFA | EUR, 500

EUR|fxFA|200,200

300|fxFA,10

USD|70648|xx,50

xx|300|USD|70648,100

EUR|500|fxFA,300

AA|70648|05|USD, 500

DD|70648|USD, 200

70648|USD|1919|EST, 150

];

final:

NoConcatenate Load * Resident table1 where NewKey&NewKey2<>NewKey and NewKey&NewKey2<>NewKey2

;

Drop Table table1;

WHAT SHOULD I DO TO COMBINE ALL THESE 3 CASES? is there an alternative to this?

Please help... stalwar1‌

sunny_talwar · ‎2017-12-18

I will have to check it out... never had this kind of scenario, but will try to play around with it to see what I get

Search field value in another field (occurance)