I have a table as follows:
CN|Lev|ref1|ref2|ref3|ref4|ref5|ref6
cn1|1|1|2|3|4|||
cn2|2|||||5|6|
The representation required is:
CN|Lev|Ref|Count
cn1|1|ref1|1
cn1|1|ref2|2
cn1|1|ref3|3
cn1|1|ref4|4
cn2|2|ref5|5
cn2|2|ref6|6
Hi @anisgupt ,
Please try below query and let me know if it helps:
| makeresults
| eval CN="cn1", Lev="1", ref1="1", ref2="2", ref3="3", ref4="4", ref5="", ref6=""
| append
[| makeresults
| eval CN="cn2", Lev="2", ref1="", ref2="", ref3="", ref4="", ref5="5", ref6="6" ]
| eval cr=mvzip(CN,Lev,"-")
| fields - _time Lev CN
| untable cr Ref Count
| rex field=cr "(?P<CN>[^\,].+)-(?P<Lev>.+)"
| table CN Lev Ref Count| where Count>0
@anisgupt Can you please accept the answer if it helped with your issue!