Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
arvind_patil
Partner - Specialist III
Partner - Specialist III

How to Acchive this in Qlikview

Hi Experts,

I have following data and Master.

1.I Need to apply master on Data table based on Combination of Fields Product  and ProductDesc .

2. Here Consider *  Means  any of the remaining value

For Ex:


This value apply  against W00341     ,W00341/K*,    0.8


Product   ProductDesc     Value   Multiply

W00341     ,W00341/KA,     1000,  0.8

W00341 ,W00341/KAM,   1000,   0.8

W00341 ,W00341/KEM,        3000,  0.8


Same Like Remaining * values

Raw Data:

Data:

Load * Inline [

Product  ProductDesc  Value

W00341     ,W00341/KA,     1000

W00341 ,W00341/KAM,   1000

W00341 ,W00341/KEM,        3000

W00341/IM ,W00341/TBR1,   4000

W00341/IM ,W00341/TBR2,   5000

W00341/IM ,W00342,     6000

W00341 ,W00341/IM,         6000

W00341, W00341/TBR1,        4000

W00341 ,W00341/TBR2,3000

];

Master:

Load * Inline [

Product  ProductDesc  Multiply

W00341     ,W00341/K*,    0.8

W00341/IM ,W00341/T*,    0.7

W00341/IM ,W00342,        1

W00341 ,W00341/IM,         .9

W00341, W00341/T*,          0.5

];

Thanks,

Arvind Patil

8 Replies
avinashelite

Try like this

Data:

LOAD * ,

Product&'-'&subfield(ProductDesc,'/',1)&'/'&left(subfield(ProductDesc,'/',2),1)&'*' as Key ;

Load * Inline [

Product,ProductDesc,Value

W00341     ,W00341/KA,     1000

W00341 ,W00341/KAM,   1000

W00341 ,W00341/KEM,        3000

W00341/IM ,W00341/TBR1,   4000

W00341/IM ,W00341/TBR2,   5000

W00341/IM ,W00342,     6000

W00341 ,W00341/IM,         6000

W00341, W00341/TBR1,        4000

W00341 ,W00341/TBR2,3000

];

LEFT JOin

Master:

LOAD Product as P,

ProductDesc as PD,

Multiply,

Product&'-'&subfield(ProductDesc,'/',1)&'/'&left(subfield(ProductDesc,'/',2),1)&'*' as Key;

Load * Inline [

Product,ProductDesc,Multiply

W00341     ,W00341/K*,    0.8

W00341/IM ,W00341/T*,    0.7

W00341/IM ,W00342,        1

W00341 ,W00341/IM,         .9

W00341, W00341/T*,          0.5

];

micheledenardi
Specialist II
Specialist II

Hi Arvind,

you have to create one common key like Product&'|'&CommonProductDesc and use it to Join or Applymap the Multiply factor.

By using * inside a key Qlik consider it as a normal character and not as "all characters".

I think this could be the only solution you have.

Michele De Nardi
If a post helps to resolve your issue, please accept it as a Solution.
arvind_patil
Partner - Specialist III
Partner - Specialist III
Author

Hi Avinash,

Firstly ,Thanks for your reply.

But in My case its always not  after slash * it may be  value like above

W00341 ,W00341/IM,         .9  in this case it may go wrong.


Thnaks,

Arvind Patil

techvarun
Specialist II
Specialist II

May be this

Data:

Load *,Left(ProductDesc,8) as Test Inline [

Product ,ProductDesc ,Value

W00341     ,W00341/KA,     1000

W00341 ,W00341/KAM,   1000

W00341 ,W00341/KEM,        3000

W00341/IM ,W00341/TBR1,   4000

W00341/IM ,W00341/TBR2,   5000

W00341/IM ,W00342,     6000

W00341 ,W00341/IM,         6000

W00341, W00341/TBR1,        4000

W00341 ,W00341/TBR2,3000

];

Master:

Load * , Left(ProductDesc,8) as Test Inline [

Product ,ProductDesc ,Multiply

W00341     ,W00341/K*,    0.8

W00341/IM ,W00341/T*,    0.7

W00341/IM ,W00342,        1

W00341 ,W00341/IM,         .9

];

Load Test, Multiply Resident Master;Drop table Master

antoniotiman
Master III
Master III

May be like this

Data:
Load * Inline [
Product, ProductDesc, Value
W00341 ,W00341/KA, 1000
W00341 ,W00341/KAM, 1000
W00341 ,W00341/KEM, 3000
W00341/IM ,W00341/TBR1, 4000
W00341/IM ,W00341/TBR2, 5000
W00341/IM ,W00342, 6000
W00341 ,W00341/IM, 6000
W00341, W00341/TBR1, 4000
W00341 ,W00341/TBR2,3000]
;

Left Join
Load * Inline [
Product, ProductDesc1, Multiply
W00341 ,W00341/K*, 0.8
W00341/IM ,W00341/T*, 0.7
W00341/IM ,W00342, 1
W00341 ,W00341/IM, .9
W00341, W00341/T*, 0.5]
;
LOAD Product,PD as ProductDesc,Value,Multiply
Where PD <> 0;
LOAD *,
If(Right(ProductDesc1,1)='*',If(ProductDesc Like ProductDesc1,ProductDesc,0),ProductDesc) as PD
Resident Data;
Drop Table Data;

HirisH_V7
Master
Master

Hi ,

Check the following script may help you,

As i hope you should get the corresponding data from the master table,to first table. so i went with left join.

So ,You will get only those 9 records and pointing multiply field from master,by this script.

Data:
LOAD *,
Subfield(ProductDesc,'/',1)&Left(Subfield(ProductDesc,'/',2),1) as DataConnect

INLINE [
Product, ProductDesc, Value,
W00341, W00341/KA, 1000
W00341, W00341/KAM, 1000
W00341, W00341/KEM, 3000
W00341/IM, W00341/TBR1, 4000
W00341/IM, W00341/TBR2, 5000
W00341/IM, W00342, 6000
W00341, W00341/IM, 6000
W00341, W00341/TBR1, 4000
W00341, W00341/TBR2, 3000
]
;

Left Join

Master:
LOAD * ,
Subfield(Replace(ProductDescMaster,'*',''),'/',1)&IF(len(Subfield(Replace(ProductDescMaster,'*',''),'/',2))>=2,
Left(Subfield(Replace(ProductDescMaster,'*',''),'/',2),1),Subfield(Replace(ProductDescMaster,'*',''),'/',2))

as DataConnect

INLINE [
Product, ProductDescMaster, Multiply
W00341, W00341/K*, 0.8
W00341/IM, W00341/T*, 0.7
W00341/IM, W00342, 1
W00341, W00341/IM, .9
W00341, W00341/T*, 0.5
]
;
//Drop Fields ProductDescMaster;

Hope this helps,

PFA for your reference.

-Hirish

HirisH
“Aspire to Inspire before we Expire!”
effinty2112
Master
Master

Hi Arvind,

Try:

Data:

Load * Inline [

Product,  ProductDesc,  Value

W00341     ,W00341/KA,     1000

W00341 ,W00341/KAM,   1000

W00341 ,W00341/KEM,        3000

W00341/IM ,W00341/TBR1,   4000

W00341/IM ,W00341/TBR2,   5000

W00341/IM ,W00342,     6000

W00341 ,W00341/IM,         6000

W00341, W00341/TBR1,        4000

W00341 ,W00341/TBR2,3000

];

Left Join(Data)

Load * Inline [

Product,  ProductDescMap,  Multiply

W00341     ,W00341/K*,    0.8

W00341/IM ,W00341/T*,    0.7

W00341/IM ,W00342,        1

W00341 ,W00341/IM,         .9

W00341, W00341/T*,          0.5

];

For i = 0 to NoOfRows('Data')-1

Let vMatch = chr(39) & Peek('ProductDescMap',$(i),'Data') & chr(39);

if Peek('ProductDesc',$(i),'Data') = Peek('ProductDescMap',$(i),'Data') or wildmatch(Peek('ProductDesc',$(i),'Data'), $(vMatch)) then

Result:

LOAD

Peek('Product', $(i),'Data') as Product,

Peek('ProductDesc', $(i),'Data') as ProductDesc,

Peek('Value', $(i),'Data') as Value,

Peek('Multiply', $(i),'Data') as Multiply

AutoGenerate 1;

End if;

Next i;

Drop Table Data;

Regards

Andrew

tresesco
MVP
MVP

Hi,

If you are looking output like below:

Capture.JPG

One way of solution could be using ApplyMap() and MapSubString() separately in two loads and concatenate them to get the desired output. PFA