Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Everyone,
I have a question. I have this table:
[TotC8]:
LOAD
[@1] AS [TotC8.Scenario],
[@2] AS [TotC8.Year],
[@3] AS [TotC8.Period],
[@5] AS [TotC8.Brand],
[@7] AS [TotC8.Account],
[@10] AS [TotC8.Market],
[@16] AS [TotC8.BusinessOrg],
[@18] AS [TotC8.HFMCode],
[@19] AS [TotC8.Amount]
FROM [lib://Desktop/TotC8.dat]
(txt, codepage is 28592, no labels, delimiter is ';', msq, header is 1 lines);
and I want have in output a table where I have all the columns without [@7] and [@19], because I want that the column [@7] is the filed and [@19] is the value of the filed.
How I can do it by using crosstable??
Thanks,
Livio
Hi,
I am not sure if it works without seeing your data and data model. But you can try this.
[Temp_TotC8]:
LOAD
[@1] AS [TotC8.Scenario],
[@2] AS [TotC8.Year],
[@3] AS [TotC8.Period],
[@5] AS [TotC8.Brand],
[@7] AS [TotC8.Account],
[@10] AS [TotC8.Market],
[@16] AS [TotC8.BusinessOrg],
[@18] AS [TotC8.HFMCode],
[@19] AS [TotC8.Amount]
FROM [lib://Desktop/TotC8.dat]
(txt, codepage is 28592, no labels, delimiter is ';', msq, header is 1 lines);
temp_AccList:
load distinct [TotC8.Account] as AccList Resident Temp_TotC8;
Main:
load distinct
[TotC8.Scenario],
[TotC8.Year],
[TotC8.Period],
[TotC8.Brand],
[TotC8.Market],
[TotC8.BusinessOrg],
[TotC8.HFMCode]
Resident Temp_TotC8;
FOR Each a in FieldValueList('AccList')
left join(Main)
load
[TotC8.Scenario],
[TotC8.Year],
[TotC8.Period],
[TotC8.Brand],
[TotC8.Market],
[TotC8.BusinessOrg],
[TotC8.HFMCode],
TotC8.Amount as '$(a)'
Resident Temp_TotC8
where TotC8.Account='$(a)';
NEXT a;
Drop Tables Temp_TotC8,temp_AccList;
Please try with
crosstable (Account,Ammount,7)
LOAD
[@1] AS [TotC8.Scenario],
[@2] AS [TotC8.Year],
[@3] AS [TotC8.Period],
[@5] AS [TotC8.Brand],
[@10] AS [TotC8.Market],
[@16] AS [TotC8.BusinessOrg],
[@18] AS [TotC8.HFMCode],
[@7] AS [TotC8.Account],
[@19] AS [TotC8.Amount]
FROM [lib://Desktop/TotC8.dat]
(txt, codepage is 28592, no labels, delimiter is ';', msq, header is 1 lines);
Ho,
with this code I see this:
It's wrong, i want see "STA1000", "STA1130" in fields and the value 0 in rows (it's the amount of the Account --> STA10000,STA1130).
would you mind sharing a sample of your data?
Hi,
I am not sure if it works without seeing your data and data model. But you can try this.
[Temp_TotC8]:
LOAD
[@1] AS [TotC8.Scenario],
[@2] AS [TotC8.Year],
[@3] AS [TotC8.Period],
[@5] AS [TotC8.Brand],
[@7] AS [TotC8.Account],
[@10] AS [TotC8.Market],
[@16] AS [TotC8.BusinessOrg],
[@18] AS [TotC8.HFMCode],
[@19] AS [TotC8.Amount]
FROM [lib://Desktop/TotC8.dat]
(txt, codepage is 28592, no labels, delimiter is ';', msq, header is 1 lines);
temp_AccList:
load distinct [TotC8.Account] as AccList Resident Temp_TotC8;
Main:
load distinct
[TotC8.Scenario],
[TotC8.Year],
[TotC8.Period],
[TotC8.Brand],
[TotC8.Market],
[TotC8.BusinessOrg],
[TotC8.HFMCode]
Resident Temp_TotC8;
FOR Each a in FieldValueList('AccList')
left join(Main)
load
[TotC8.Scenario],
[TotC8.Year],
[TotC8.Period],
[TotC8.Brand],
[TotC8.Market],
[TotC8.BusinessOrg],
[TotC8.HFMCode],
TotC8.Amount as '$(a)'
Resident Temp_TotC8
where TotC8.Account='$(a)';
NEXT a;
Drop Tables Temp_TotC8,temp_AccList;
Hi,
Thanks it is correct. But now I have another problem:
I have the values of the Online Org Orgs, but each PIPPO is associated with one or more ACCOUNTS. I would like to report the accounts in the column and associate the reference BusinessOrg to each one.
For example:
and I want 2 columns like : STA1000_TotC8 and STA1000_BOPE.
Thanks