Pivot and Chart-Please help!!!

[MARU]

I have attached an excel file that I do weekly with an updated refreshed data.  I wanted to mimic this in Dataiku for automation.  I don't know much coding so using the prepare recipe but its not working.  I need help.  

In my attached file, I have a raw extract where I want to "transpose" the dates into rows with naming that column "date".  Then I wanted the column "P to be  the columns with the Metric.  Then I wanted to do the basic chart that looks like that with dates on X axis and filter by "P" and use column "Metric".   but unable to do it.  I need help!!!  I would appreciate a step by step approach since I'm new this and trying to learn. 

[Berilio]

Hi, in prepare recipe set "fold multiple columns by pattern" step to convert data columns in rows, and next parse date step.

Then in chart tab you can show values(sum) by date and Metric  and you can filter by P.

[MARU]

Hi I used the "fold method" that you showed in the pic but don't understand how are you getting the "Columns to fold pattern"  .*?\d\d\d\d\d\d\.  It is not working for me at all.  I tried doing the "find with Smart pattern" but it is not working.  I attached the screenshot.

[CoreyS]

Hi @MARU thank you for asking your questions on the Dataiku Community! If you haven't already, I'd love to point you to our Learning Paths on the Dataiku Academy. The Dataiku Academy Learning Paths guides your learning on Dataiku from your first steps through progressive mastery of the platform. Complete the learning path and prove your skills by taking the certification exams.

I hope this helps in your data journey here utilizing Dataiku!

[MARU]

I have completed the basic trainings but I'm not sure what regex code is in the fold and Dataiku notes are not helpful as well.

[CoreyS]

Hi @MARU thank you for your feedback. Here are some additional resources you may find helpful:

• Concept: Regular Expressions in Dataiku
• Hands-On Tutorial: Advanced Formula & Regular Expressions

I hope this helps!

[MarkPundurs]

Things we learned while making this work:

• The docs need to be better integrated. Some of the info we needed was at https://doc.dataiku.com/dss/9.0/preparation/processors/fold-columns-by-pattern.html and some at https://knowledge.dataiku.com/9.0/courses/advanced-data-prep/prepare-recipe/smart-pattern-builder.ht.... 
• To select column name text in the Smart Pattern window, one should click-and-drag rather than multi-click; for example, if a column is named 2020/01/01, triple-clicking 2020 will highlight the entire column name but only 2020 will register as the text selection on which to base the pattern. This point and the following one do not appear to be documented anywhere.
• Depending on what one enters in the Smart Pattern window as a custom regex, Dataiku may add to it when you click OK - that is, what appears in the processor field may not be identical to what was typed in the Smart Pattern window.

[CoreyS]

Thank you for sharing your feedback and solution @MarkPundurs. We’ll be sure to surface your feedback for review. Thanks again.

[Berilio]

Hi, this pattern is for 0000/00/00 format. First I click on blue words "Find with Smart Pattern" and then i write on "write your own regex here..." box.

Maybe you can try with ([0-9]{4}/[0-9]{2}/[0-9]{2}) pattern. This should work  too.

I attached a screenshoot.

Regards