adding columns after crosstable

[#1]

I need to add a few columns after I pivoted an excel spreadsheet and I can’t find indications about where I should put the instructions.

I tried the following but the columns get added as rows (the get pivoted) J

CrossTable(KPI, Data)

LOAD F1 as "Date",

     [Number of Project] as [Number of Projects] ,

"Number of Project" / 10 as "Number of projects - KPI"

FROM…

[reddy-s]

Hi Olivier,

try a preceding load instead:

Data:

load *,

     "Number of Project" / 10 as "Number of projects - KPI";

CrossTable(KPI, Data)

LOAD F1 as "Date",

     [Number of Project] as [Number of Projects] ,

"Number of Project" / 10 as "Number of projects - KPI"

FROM…

[#3]

Nice! preceding loads look promising

I tried

[Perf KPI]:

Load *,

  year("Date") as "Year";

CrossTable(KPI, Data)

LOAD

     [Date] as [Day],

     [Number of Project] as [Number of Projects]

FROM...

but got Field not found - <Date>

😞

Sorry for my basic questions but I'm a total newbie and all that scripting is pretty cryptic to me 😉

[#4]

I tried with "Day" instead of "Date" but then the data doesn't get unpivoted (crosstable doesn't get applied)

[Perf KPI]:

Load *,

  year("Day") as "Year";

CrossTable(KPI, Data)

LOAD

     [Date] as [Day],

     [Number of Project] as [Number of Projects]

FROM...

but

[reddy-s]

Hi Olivier,

as you have renamed the Date field you have that error.

Use this:

[Perf KPI]:

Load *,

  year([Day]) as "Year";

CrossTable(KPI, Data)

LOAD

     [Date] as [Day],

     [Number of Project] as [Number of Projects]

FROM...

[#6]

Thanks Sangram for your inputs, both versions work (year("Day") / year([Day]) but in both cases the data doesn't get unpivoted anymore 😞

[#7]

my WHERE at the end doesn't get applied either when the preceding load instructions are included but works fine as soon as I remove it

WHERE NOT isnull("Date")

[reddy-s · Accepted solution]

Hi Olivier,

Try this,

data:

CrossTable(KPI, Data)

LOAD

     [Date] as [Day],

     [Number of Project] as [Number of Projects]

FROM...

additional_data:

load *,

       year(Day)  as yearName

       resident data;

drop table data;

Once this is done, you should have the data in the "additional_data" table with an additional field "yearName"

If this docent solve the qvf, it would be easy if you attach the excel and the qvf/qvw?

[reddy-s]

Is it possible for you to attach the qvf?

[#10]

Yahoo! It worked. You saved my life today (+ I learnt 2 ways to alter the data after the initial load: preceding vs Resident)

Thank you so much for your help Sangram!