Analyst 8.4 FP2: D-List object order when importing from text file

[mr j]

Hi,

after my first post which was solved I'm encouraged to post another even though maybe in the 'newbie' category... I just don't get around to solve the following:

I'd need to automatically update monthly a D-List which looks appr. like this:

Feb-11
Feb-11 Total (subtotal of the following weeks)
Feb week 1
Feb week 2
Feb week 3
Feb week 4
Mar-11
Mar-11 Total (subtotal of the following weeks)
Mar week 1
Mar week 2
Mar week 3
Mar week 4
Apr-11
Apr-11 Total (subtotal of the following weeks)
Apr week 1
Apr week 2
Apr week 3
Apr week 4
May-11
Jun-11
Jul-11... etc. for an amount of months without subtotals

Every month the D-list is rolled one month forward, the first three months are split to week level and the subtotal calculated for these + there should be a column for the same month without the subtotal BUT preceding the subtotal.
Source would be text file or more likely DW database.
D-list is used in a Contributor application as columns and should have this order of items (which is probably another story with 8.4, but would like to see this step work in Analyst too.)

Now the issue is how to get exactly this order in the update import? In "D-List options - Import link - Location/Sort Option" there's "Source Order" which implies that what you see in the source file is what you get in the D-List, but apparently this is not so (This option is not introduced in the user guide, btw). I tried this as a manual operation with a tab separated file with two columns, one for items and one for the parent, but the order of items is not correct. Week level stuff goes on top, subtotals after them, rest after that. Ticked "Update" and "Remove Obsolete Items".

Manual reorder after every monthly import is not an option.

Ideas, you gurus?

Thanks!

[kbrauer]

Hi
The only way to do what you want is as follows
Create a spreadsheet with 4 columns

ItemNo     ParentNo    Item                  Parent
    1                              Jan
    3                  2          Jan Week 1       Jan Total
    4                  2          Jan Week 2       Jan Total
    5                  2          Jan Week 3       Jan Total
    6                  2          Jan Week 4       Jan Total
    7                              Feb
    9                  8          Feb Week 1       Feb Total
   10                 8          Feb Week 2       Feb Total
   11                 8          Feb Week 3       Feb Total
   12                 8          Feb Week 4       Feb Total
   13                             Mar
   15                14         Mar Week 1       Mar Total
   16                14         Mar Week 2       Mar Total
   17                14         Mar Week 3       Mar Total
   18                14         Mar Week 4       Mar Total
   19                             Apr
   20                             May
etc

Make an ODBC connection to the spreadsheet

The following SQL will then give you the result (this is for Excel)

Select format$(ItemNo,'00')+' '+Item as ITEMNAME, format$(ParentNo,'00')+' '+Parent as ParentName

Import the result alphabetical with remove obsolete

Regards

Kim

[mr j]

Hi

Cool, thank you so much!

BUT, this gives a D-list with the two numbers in front of items. For eLists in Contributor you can edit the display name, but for D-lists I don't think you can. Some other workaround to accomplish items without the numbers..?

I'm still interested to hear what's the idea of "Source Order" in the menu "Import link - Location/Sort Option" if it's not to give you what you see in the source.