Nested Data

[qais1975]

Dear All,

I am trying to import https://stats.oecd.org/Index.aspx?datasetcode=SNA_TABLE1_ARCHIVE

data regarding the GDP. However, the data has different nested level. The transaction for each country has three categories and at each category there are several category. For instance, the B1_GA: Gross domestic product (output approach) has three categories (B1G_P119: Gross value added at basic prices, excluding FISIM, D21_D31: Taxes less subsidies on products, and DB1_GA: Statistical discrepancy). The B1G_P119: Gross value added at basic prices has one category which is B1G: Gross value added at basic prices, total activity which has three sub-category. 

My question will be there any way to read this data in Alteryx so I can do some predictive analysis as well as some data preprocessing? 

I really appreciate your help.

Qais 

[Yoshiro_Fujimori]

@qais1975 ,

I guess you want to convert from the below input...

... to something like this;

Assuming that is what you want, here is one way of doing it.

After cleansing the table, use Multi-Row formula to fill in the category of one-level-up.

Does it work for your case?

[qais1975]

Dear @Yoshiro_Fujimori ,

Thank you for your input. However, I really do not know from where you get these F1, F2, and so on. Do you use a query to import the data?

I download the data as CSV file and when I run your workflow I got an error about F's that you renamed them to Lv's.

Please let me know how did you download the data as it might be this is the issue.

Best,

Qais 

[Yoshiro_Fujimori]

@qais1975 ,

As you may be aware, CSV file does not work as it has only one transaction category field.

So you should downloaded it as Excel format.

(This might not be the Alteryx issue, but an issue specific to the site you mentioned.)

 

When you open the saved file, you would see an error "The file format and Extension don’t match”.

Respond with Yes to open the file, and then "Save As" specifying the format as .xlsx.

(It seems the file format is originally ".xml spreadsheet" instead of ".xlsx".)

After you save the file in .xlsx format, my workflow would accept it.

[qais1975]

Dear @Yoshiro_Fujimori ,

That is great. One more thing, as I noticed that you used the group by option after the second multi-row formula. However, the GDP data is classified for each country, measure, unit and year. If you see column Lv1 which lists these indicators and there values as I see them comes after Lv5. Therefore, How do I keep these values since each counrty in the database has different measures as I am showing below. Can we categorize the data for each country and each measure inside each country? By the way I just remove the group by selection so I can keep these indicators. 

[Yoshiro_Fujimori · Accepted solution]

@qais1975 ,

You may try this way:

1. "Select Records" for the desired records

2. "Cross Tab" to make them to one row

3. "Append Field" to the existing table

Good luck.

[qais1975]

Dear @Yoshiro_Fujimori ,

You are amazing and thank you so much for this phenomenal help and support. I really highly appreciated your response and I will keep you as my golden hero in Alteryx.

Best,

Qais