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!
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
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.