Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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…
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?
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…
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 😉
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
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...
Thanks Sangram for your inputs, both versions work (year("Day") / year([Day]) but in both cases the data doesn't get unpivoted anymore 😞
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")
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?
Is it possible for you to attach the qvf?
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!