Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

OmarBenSalem
Esteemed Contributor

Search field value in another field (occurance)

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 !

1 Solution

Accepted Solutions
OmarBenSalem
Esteemed Contributor

Re: Search field value in another field (occurance)

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;

34 Replies

Re: Search field value in another field (occurance)

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

OmarBenSalem
Esteemed Contributor

Re: Search field value in another field (occurance)

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..

Re: Search field value in another field (occurance)

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

Re: Search field value in another field (occurance)

You're a life savior Sun !

OmarBenSalem
Esteemed Contributor

Re: Search field value in another field (occurance)

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

Re: Search field value in another field (occurance)

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

Re: Search field value in another field (occurance)

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) :

Capture.PNG

prblem 1 :

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

problem 2 :

Synthetic keys generation

Capture.PNG


How could/should I optimize this?

Thanks ! stalwar1

OmarBenSalem
Esteemed Contributor

Re: Search field value in another field (occurance)

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;

Capture.PNG

NOT CORRECT

Capture.PNG

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

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;

Capture.PNG

Capture.PNG

Not correct :

Capture.PNG

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

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;

Capture.PNG

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

Please help... stalwar1

Re: Search field value in another field (occurance)

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

Community Browser