Crosstable Qlik Sense

[livio218526]

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

[Clever_Anjos]

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);

[livio218526]

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

[Clever_Anjos]

would you mind sharing a sample of your data?

[kaanerisen · Accepted solution]

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;

[livio218526]

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