row to column data

[baghu]

Hi,

I'm importing a csv file as a database and I've provided the input and output file for your further reference.

Input file

SCAN# PAGE# code code level
1 7044 99213 Level 1
99053 Level 2
1 7048 99213 Level 1
99053 Level 2
1 7049 99214 Level 1
87804 Level 2
99058 Level 3
1 7089 99204 Level 1
87880 Level 2
99051 Level 3
1 7093 99204 Level 1
94640 Level 2
94640 Level 3
99051 Level 4

Output file as required.

SCAN# PAGE# code code level code 2 code 3 code 4
1 7044 99213 Level 1 99053
1 7048 99213 Level 1 99053
1 7049 99214 Level 1 87804 99058
1 7089 99204 Level 1 87880 99051
1 7093 99204 Level 1 94640 94640 99051

Whenever I see Level 2, Level 3, Level 4 etc. in the code level column, I need to copy the data under the CPT column and paste it as a new column like code 2, code 3 etc.

I'm using Monarch 10.5 as the version. I do not want to export this data as a .txt file as I have another 50 columns with the same database input file.

Any help is appreciated and also let me know how to attach a file in this forum.

Regards
Baghavathy

[OllyInMunich]

Hello baghu,

This looks straightforward if you import the CSV file as a report instead of as a database. Just changing the file extension to ".prn" should tell Monarch to treat it as a text report.

Then one detail template, using the floating trap, on the "Level" for the detail, and an Append template, on the 1 7084 characters. This will get the data you need into a table.

After that, a summary with the Level as an across key and a measure of Unique(Value) should give you what you need. Make sure the Value field (e.g. 99051) is defined as numeric.

This assumes that the value field is always numeric, that you don't need to preserve leading zeroes, and that you have one value only at each level for each code.

Best wishes,

Olly

[baghu]

Hello Olly,

Thanks for reply.

I may not be in a position to use your suggestion as once I import the data into the report window, I'll be using many functions/rules/etc. Also, as I've mentioned earlier, I may not be in a position to convert this into a .txt or .prn file as I've to convert more than 500 columns, which will be difficult.

The summary window will not help me to do the calculations.

Regards
Baghavathy

[OllyInMunich]

Hello Baghavathy,

If you have a CSV file, you can rename it as a PRN file regardless of how many columns there are. If you have more than 254 columns you won't be able to import them into Monarch in one model.

There's nothing in the approach I proposed to stop you from adding filters and calculated fields to the data in the table window, to help you export exactly what you need from the summary window.

HTH

Olly