Qliksense tutorial: Data cleaning using Wildmatch, For loop & Variables in Qlik Sense

Подписаться 5 тыс.

Просмотров 3,6 тыс.

50% 1

🎓 Get the Qliksense masterclass : www.thinkmetri...
📕 Get the Formula CheatSheet: www.thinkmetri...
Build your dashboard tutorial: • Build your first app i...
Sign up for Qlik business free 30 days trial to learn if you don't have access to Qlik : www.qlik.com/u...
****************************************************************************
#qlik #qliksense #dataviz
**********************************************************************
Struggling to get your work done? Check out:
📓 Lazy Man's Guide to productivity, become super productive: gumroad.com/l/...
🚀 Productivity Masterclass: Take my Master course on Overcoming procrastination from a former serial procrastinator: www.udemy.com/...
📚 Read on Kindle the simplest way to become ultra-productive:
www.amazon.com...
📸 Instagram - / kaushms
🐦 Twitter - / kaush_ms
WHO AM I:
🙋🏻‍♂️ I'm Kaush, a Data Analyst and Data Visualization Developer working for a tech company. I live and work in Sweden. I make videos about technology, productivity, and lifestyle design. Say hi to me on Twitter 😃

Опубликовано:

6 окт 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 12

@thinkmetrics 2 года назад

🎓 Get the Qliksense masterclass : www.thinkmetrics.se/masterclass 📕 Get the Formula CheatSheet: www.thinkmetrics.se/cheatsheet

@wendywu5359 2 года назад

Very good tips! Exactly what I am looking for!

@thinkmetrics 2 года назад

Thanks, Wendy 🙌🏻

@danayoung8756 2 года назад

Thanks for posting! gives me some ideas for my own work. Question - Could you accomplish the same by using ifmatch?

@thinkmetrics 2 года назад

Hi. That could work but a bit more work that way

@manikanteswarreddyn209 2 года назад

Awesome,,, 🙌🏻

@thinkmetrics 2 года назад

🙌🏻

@472Felipe 2 года назад

Key Kaush, recently watched this video and I really liked it, but it got me thinking about, what if I have a bigger table so the solution using loop could be a little bit slow, so I thought about doing it using Concat to bring all the possibilities in a variable. Let me know what do you think (My first language is portuguese, don't mind it hahaha) MAP_TMP: LOAD *, Rowno() as Ordem ; LOAD * INLINE [ De , Para STRIAS , INDÚSTRIAS LABORATORIOS , LABORATORIOS HOSPITAIS , HOSPITAIS FORNECEDORES DIVERSOS , FORNECEDORES DIVERSOS DISTRIBUIDORES , DISTRIBUIDOR CONSUMIDOR FINAL , CONSUMIDOR FINAL CLINICA , CLÍNICA ]; NOCONCATENATE MAP_CANAL: LOAD Concat(Chr(39) & '*' & De & '*' & Chr(39),',',Ordem) as De, Concat(Chr(39) & Para & Chr(39),',',Ordem) as Para RESIDENT MAP_TMP; DROP TABLE MAP_TMP; LET vDe = PEEK('De'); LET vPara = PEEK('Para'); DROP TABLE MAP_CANAL; LOAD *, PICK(WILDMATCH(endereco_loja,$(vDe)),$(vPara)) as CANAL FROM xxx

@thinkmetrics 2 года назад

Amazing 🤩. I haven’t tested using concat but looks like a cool solution. Great job Filipe👏🏻 🙌🏻

@workwithqlik1642 2 года назад

To break it down for my own understanding, in Map_temp table you are trying to load the row no to create a primary key (ID) for the identification of data present in the load inline table. Then in the map_canal table you are trying to create a composite key with the two columns para and de. Then removing unnecessary tables using drop table. followed by the loading of the data from the map canal table into a final table using pick and wildmatch, in which the $vDel value is used for matching and vPara as the expression to be used in place of the De column. Let me know if its correct or not?

@472Felipe 2 года назад

@@workwithqlik1642 You got it right. The ideia for the RowNo() is to make sure that when you use the Concat function to group all the values for each of the columns, to use the ID made by Rowno() as the third argument on the Concat function, to make sure that it'll group using the same order from the Tmp Table, because the order does matter when using the pick and match function. In the Map_Canal table it's not trying to create a composite key, instead it's just making the 2 fields necessary for the Pick and Wildmatch, being "De" the field for the WildMatch function and "Para" as the field for the Pick function.

@workwithqlik1642 2 года назад

@@472Felipe ok, thanku for making it more clearer