Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
];
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.
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
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
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;
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
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
Hi,
If you are looking output like below:
One way of solution could be using ApplyMap() and MapSubString() separately in two loads and concatenate them to get the desired output. PFA