convert the list into array

Likely the answer will be to build a view in the database that displays the data in the appropriate format but the need to output in that very particular text format confuses me. At the very least if that format is required for some reason I'd still output in a CDT and then have a transformation expression rule.

If this hasn't answered your query please provide more details and the expression output of the a!queryEntity()

my requirement is upon giving the doctor name  i need to get all the reasons for visit for that particular doctor with a comma separated like eg:{cancer,Gout,fever}. I need to get the reasonfor visit in a array format

When you say "upon giving the doctor name..." - maybe you could pass the doctor name into the Query Entity rule as a filter, then the resulting list would only be visits to that particular doctor, and you could arrange the "reasonForVisit" properties in whatever way you require?  Or are you after some sort of enumeration of ALL doctors followed by an array of ALL visit reasons for that doctor, all at once?  This is trickier but should be doable.

thank you mike for your reply, can you please give me more description on how to convert the list into array

thank you mike for your reply, can you please give me more description on how to convert the list into array

mamathak0001 You may have to create a View on your PO_MEDICALINFO table using GROUP CONCAT Function  as

SELECT reportingDoctor, count(1), GROUP_CONCAT(DISTINCT reasonForVisit SEPARATOR ',') FROM PO_MEDICALINFO 

www.mysqltutorial.org/.../

Note: Ensure you are testing this view performance with a considerable amount of data in PO_MEDICALINFO  table

Hi mamathak,

Does this help? I stepped through each transformation very slowly so that there wasn't one big expression that is hard to pick apart. Let me know if this hits the mark.

Please note I've assumed that the 'reasonForVisit' is always delimited by a semicolon and then a space "; " and that you want this to be a unique list of all reasons for each doctor.

a!localVariables(
  local!data: {
    {
      reportingDoctor: "Meenakshi",
      patientCount: 1,
      reasonForVisit: "Cancer"
    },
    {
      reportingDoctor: "Meenakshi",
      patientCount: 1,
      reasonForVisit: "Gout; Fainting Spells"
    },
    {
      reportingDoctor: "Sriram",
      patientCount: 1,
      reasonForVisit: "Diabetes; High Blood Pressure; Other"
    },
    {
      reportingDoctor: "Sai Kiran",
      patientCount: 1,
      reasonForVisit: "Heart Disease"
    },
    {
      reportingDoctor: "Meenakshi",
      patientCount: 1,
      reasonForVisit: "Gout"
    },
    {
      reportingDoctor: "Sai Kiran",
      patientCount: 1,
      reasonForVisit: "Gallstones"
    },
    {
      reportingDoctor: "Sai Kiran",
      patientCount: 1,
      reasonForVisit: "Fainting Spells"
    }
  },
  /*Get the unique list of doctors*/
  local!uniqueListOfDoctors: reject(
    fn!isnull,
    union(
      touniformstring(
        index(
          local!data,
          "reportingDoctor",
          {}
        )
      ),
      tostring({})
    )
  ),
  /*Iterate through this list of doctors to construct the string*/
  a!forEach(
    items: local!uniqueListOfDoctors,
    expression: a!localVariables(
      /*Get the indexes of were this doctor is in the data*/
      local!indexesOfThisDoctor: wherecontains(
        fv!item,
        touniformstring(
          index(
            local!data,
            "reportingDoctor",
            {}
          )
        )
      ),
      
      /*Get the data for this doctor*/
      local!dataForThisDoctor: index(
        local!data,
        local!indexesOfThisDoctor,
        {}
      ),
      
      /*Get the number of times this docter appears in the data*/
      local!countForThisDoctor: length(local!dataForThisDoctor),
      
      /*Get the reasons for this visit for this doctor (In a "raw" format where some entries may be delimited by "; " */
      local!reasonsForVisitRaw: index(
        local!dataForThisDoctor,
        "reasonForVisit",
        {}
      ),
      
      /*Turn the raw reasons for a visit into an array*/
      local!reasonsForVisitArray: touniformstring(
        a!flatten(
          a!forEach(
            items: local!reasonsForVisitRaw,
            expression: split(
              fv!item,
              "; "
            )
          )
        )
      ),
      
      /*Reasons may be duplicated so get a unique list*/
      local!reasonsForVisitUniqueArray: reject(
        fn!isnull,
        union(
          local!reasonsForVisitArray,
          tostring({})
        )
      ),
      
      /*Convert the unique array into a string*/
      local!reasonsForVisitAsString: joinarray(
        local!reasonsForVisitUniqueArray,
        ","
      ),
      
      /*Construct the final string*/
      concat(
        fv!item,
        ":",
        local!countForThisDoctor,
        ":{",
        local!reasonsForVisitAsString,
        "}"
      )
      
    )
  )
)

This is along the lines of what I was thinking of suggesting. 

My only personal code-cleanliness nitpicks would be 1) to avoid calling reject() at all costs, instead preferring to call a!forEach and return "{}" for any empty entries as necessary, and 2) avoid calling "union()" against an empty set to de-duplicate, instead preferring to set up a helper rule in advance such as "rule!APN_Distinct()" which takes an array input and unions it against itself (this by itself reduces some chunks of code from 4 or 5 lines to 1). 

[And of course, my personal soap-box position STILL maintains that optimally-readable code should use property() when getting a CDT property instead of index(), which should be used to get indexes (i.e. a positional index of an array).  But around here you'd think nobody's ever even heard of property()... ]

Haha, I hadn't heard of property()!! Thanks.

I agree with everything you've said. I've used reject() since before a!forEach() existed but never thought of using apply() either so I've just always used it. I might spawn different discussions (but also search for other discussions first) to dive deeper into why one is better than the other.

Hi Mike, looked further into property() versus index() and I can see your crusade over quite some time! Unfortunately you've not gained a new follower I didn't know property() ever existed and I've never had difficulty in discerning what was being indexed and in my mind positional indexing is simply one type of index.

I get your point but it just doesn't solve any problem for me. Especially as I abstract away nested indexes into a rule I stole from Stefan (He calls it deep index). Converting to an exact replica of the same function just with another name doesn't hold much value and would break consistency in my environment which would actually lead to more confusion and harder to read code.

Good luck spreading the good word!

thank you ajhick it helped me a lot, but in the end I have used a dummy CDT to pass my values into the grid

thanks you vimalkumars

ajhick said:

Especially as I abstract away nested indexes into a rule I stole from Stefan

This principle works fine until you inherit a large project developed hastily by mainly junior devs who left behind dozens of interfaces with 5-layer-deep-nested index() calls (some of which being indexes and some of which being properties).  Trust me that it becomes a significant pain, both to read and to untangle.

Given that using the name-appropriate rule (since there is one, though no idea why this is news to so many people), has at least "slight" advantages in readability, and literally no disadvantages, I see no reason to endorse having developers continue writing unclear code just to save from typing an extra 2 letters for their function call.