Denormalising CSV tables

Keith Clarke keith.clarke at me.com
Sun Mar 15 20:24:35 EDT 2015


Thanks Peter - definitely time to learn more about arrays! :-)
Best,
Keith..

keith.clarke at improvedapps.com

> On 15 Mar 2015, at 15:30, Peter Haworth <pete at lcsql.com> wrote:
> 
> OK, then I think the array approach is the way to go. But you'll still need
> to parse the files which could be pain. Get yourself a copy of Alex
> Tweedly's csv file parser, it takes care of all the gotchas.
> 
> Another approach might be to create an sqlite database with the 4 tables in
> it, load the data into it and then select it as mentioned and create the
> denormalized csv file yourself
> 
>> On Sun, Mar 15, 2015, 8:21 AM Keith Clarke <keith.clarke at me.com> wrote:
>> 
>> Sadly, I have no control over the source CSVs - they are from a backup zip
>> file.
>> 
>> I’m trying to denormalise the key data down to the most detailed level in
>> LiveCode to simplify a single import into Excel - to enable pivot table
>> reporting without having to reconstruct the relationships in Excel using
>> Vlookups.
>> Best,
>> Keith..
>> 
>>> On 15 Mar 2015, at 15:14, Peter Haworth <pete at lcsql.com> wrote:
>>> 
>>> Are you creating the csv file yourself? If so, it should be easy to use a
>>> Select statement with JOINs to make one row for each group of related
>>> entries from the four tables.
>>> 
>>>> On Sun, Mar 15, 2015, 6:59 AM Keith Clarke <keith.clarke at me.com> wrote:
>>>> 
>>>> Thanks for the steer, Mike. I’ve not really used arrays before, but this
>>>> sounds a more efficient approach - so I’ll wok through the lessons &
>> docs.
>>>> Best,
>>>> Keith..
>>>> 
>>>>> On 15 Mar 2015, at 13:21, Mike Doub <mikedoub at gmail.com> wrote:
>>>>> 
>>>>> Basically createa direct access to all the child tables.
>>>>> 
>>>>>> On Sunday, March 15, 2015, Mike Doub <mikedoub at gmail.com> wrote:
>>>>>> 
>>>>>> Would it not be better to make the child table into an array indexed
>> by
>>>>>> its ID, then make a single pass thru the parent table using the child
>> ID
>>>>>> into the array.
>>>>>> 
>>>>>> Mike
>>>>>> 
>>>>>> On Sunday, March 15, 2015, Keith Clarke <keith.clarke at me.com
>>>>>> <javascript:_e(%7B%7D,'cvml','keith.clarke at me.com');>> wrote:
>>>>>> 
>>>>>>> Folks,
>>>>>>> Is there a ‘smart’ way to denormalise ‘child' & ‘parent' CSV tables
>>>>>>> extracted from an RDBMS in LiveCode or is it down to iterating
>> through
>>>>>>> lists using chunk functions?
>>>>>>> 
>>>>>>> I have four tables, related in the database as
>>>>>>> Child-Parent-GrandParent-GreatGrandParent. Each file has its own
>>>> record ID
>>>>>>> & the ID of its direct parent. I need to create a ‘DenormalisedChild'
>>>> file
>>>>>>> with GrandParent & GreatGrandParent IDs appended to each row.
>>>>>>> 
>>>>>>> I’m currently assuming the only way forward is to build a
>>>>>>> tDenormalisedChild variable by repeated iteration at each ‘level’:
>>>>>>> Iterating through lines of the Child file to compare its
>> ChildParentId
>>>>>>> with each row in the the Parent file, until ParentRowId =
>>>> ChildParentId;
>>>>>>> Repeat (1) on Parent file rows to find ParentGrandParent values;
>>>>>>> Repeat (1) on GrandParent file rows to get GreatGrandParent values
>>>>>>> 
>>>>>>> Is their a more efficient approach?
>>>>>>> Best,
>>>>>>> Keith..
>>>>>>> _______________________________________________
>>>>>>> use-livecode mailing list
>>>>>>> use-livecode at lists.runrev.com
>>>>>>> Please visit this url to subscribe, unsubscribe and manage your
>>>>>>> subscription preferences:
>>>>>>> http://lists.runrev.com/mailman/listinfo/use-livecode
>>>>>> 
>>>>>> 
>>>>> _______________________________________________
>>>>> use-livecode mailing list
>>>>> use-livecode at lists.runrev.com
>>>>> Please visit this url to subscribe, unsubscribe and manage your
>>>> subscription preferences:
>>>>> http://lists.runrev.com/mailman/listinfo/use-livecode
>>>> 
>>>> 
>>>> _______________________________________________
>>>> use-livecode mailing list
>>>> use-livecode at lists.runrev.com
>>>> Please visit this url to subscribe, unsubscribe and manage your
>>>> subscription preferences:
>>>> http://lists.runrev.com/mailman/listinfo/use-livecode
>>> _______________________________________________
>>> use-livecode mailing list
>>> use-livecode at lists.runrev.com
>>> Please visit this url to subscribe, unsubscribe and manage your
>> subscription preferences:
>>> http://lists.runrev.com/mailman/listinfo/use-livecode
>> 
>> 
>> _______________________________________________
>> use-livecode mailing list
>> use-livecode at lists.runrev.com
>> Please visit this url to subscribe, unsubscribe and manage your
>> subscription preferences:
>> http://lists.runrev.com/mailman/listinfo/use-livecode
> _______________________________________________
> use-livecode mailing list
> use-livecode at lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode




More information about the use-livecode mailing list