Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
livio218526
Creator
Creator

Crosstable Qlik Sense

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

1 Solution

Accepted Solutions
kaanerisen
Creator III
Creator III

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;

View solution in original post

5 Replies
Clever_Anjos
Employee
Employee

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
Creator
Creator
Author

Ho,

with this code I see this:

Capture.JPG

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
Employee
Employee

would you mind sharing a sample of your data?

kaanerisen
Creator III
Creator III

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
Creator
Creator
Author

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:

Capture.JPG

and I want 2 columns like : STA1000_TotC8 and STA1000_BOPE.

Thanks