Pivot Table Counts

[Anonymous]

I create a Pivot table that shows counts per a Unique ID.

For example

ID           Main      South       North     West

1             12           25             17         2

2             40          50               60         1

But I also have a few records that are blank and therefore are listed in my pivot under the totals, looks like below. What I would like to do is create a column ID that is blank that captures the counts that do not have an ID

Current Results

ID         State       Main      South       North     West

Totals                      500        400           300          100

                                  1               3

1                KY          12           25             17         2

2                KY            40          50               60         1

Would like to see...

ID          State       Main      South       North     West

Totals                     500        400           300          100

                                1               3

1               KY          12           25             17         2

2               KY         40          50               60         1

                Z_Mail     1             3

[Gysbert_Wassenaar]

You can replace the blank in the script. Something like this:

LOAD

     ID,

     if(Len(Trim(State))=0,'Z_Mail', State) as State,

     ...other fields...

FROM ...source...