topic Re: How to transpose data in QlikView

How to transpose data

Anonymous — Fri, 23 Feb 2018 16:03:57 GMT

Hi,

I have currently got data in this format.

I need to be able to transpose the data into this format in order to make it usable. This will be live data flowing in so it needs to be able to automatically update when new data comes in.

Thanks

Re: How to transpose data

sunny_talwar — Fri, 23 Feb 2018 17:43:04 GMT

Check this out

The Crosstable Load

Re: How to transpose data

MarcoWedel — Sat, 24 Feb 2018 16:41:30 GMT

Please post some sample data to demonstrate.

Thanks

Marco

Re: How to transpose data

Anonymous — Mon, 26 Feb 2018 10:19:03 GMT

I have posted example data. The first image is how the data is flowing into the spreadsheet, the second is what I need to transpose to.

Re: How to transpose data

MarcoWedel — Mon, 26 Feb 2018 22:23:30 GMT

Can you post in a format that can be loaded in QlikView?

Re: How to transpose data

pablolabbe — Wed, 28 Feb 2018 09:17:17 GMT

When applicable please mark the appropriate replies as CORRECT. This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution. Please mark threads as HELPFUL if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads as HELPFUL if you feel additional info is useful to others

Re: How to transpose data

jonathandienst — Wed, 28 Feb 2018 09:26:28 GMT

>>I have posted example data. The first image...

It is not possible to properly demonstrate a solution based on an image. I suggest that you upload a small sample source data document if you want detailed assistance.

Re: How to transpose data

chinnuchinni — Wed, 28 Feb 2018 10:14:38 GMT

HiJordan,

Try with this ,i hope you will get

Abc:

CrossTable(Letter, BRAg, 2)

LOAD Organisation, Team, "A;[Breakfast]", "A;[Lunch]", "A;[Dinner]", "A;[Overnight]",

"B;[Breakfast]", "B;[Lunch]", "B;[Dinner]", "B;[Overnight]"

FROM (ooxml, embedded labels, table is Sheet1);

xyz:

load Organisation,

Letter,

BRAg,

Team,

Left(Letter,1)as Letter1 ,

replace(Mid(Letter,4),']','')as session Resident Abc ;

DROP Table Abc ;

EXIT SCRIPT ;

Re: How to transpose data

Anonymous — Wed, 28 Feb 2018 10:20:39 GMT

https://docs.google.com/spreadsheets/d/e/2PACX-1vQSt29RQzxgwTnYC30CHh0ThZZL7q0TbfJxjczTaUzaPfzJu-Wdd1EtmRYy6BhLPhHd3goyTTerghZM/pubhtml?gid=0&single=true

Re: How to transpose data

Anonymous — Wed, 28 Feb 2018 10:20:55 GMT

https://docs.google.com/spreadsheets/d/e/2PACX-1vQSt29RQzxgwTnYC30CHh0ThZZL7q0TbfJxjczTaUzaPfzJu-Wdd1EtmRYy6BhLPhHd3goyTTerghZM/pubhtml?gid=0&single=true

Re: How to transpose data

MarcoWedel — Wed, 28 Feb 2018 21:16:26 GMT

Hi,

one solution could be:

table1:
CrossTable (Title,BRAG,3)
LOAD * FROM [https://docs.google.com/spreadsheets/d/e/2PACX-1vQSt29RQzxgwTnYC30CHh0ThZZL7q0TbfJxjczTaUzaPfzJu-Wdd1EtmRYy6BhLPhHd3goyTTerghZM/pubhtml?gid=0&single=true] (html, codepage is 1252, embedded labels, table is @1, filters(Remove(Row, Pos(Top, 1)),Remove(Col, Pos(Top, 1))));

Join
LOAD Distinct
    Title,
    SubField(Title,';',1) as Letter,
    TextBetween(SubField(Title,';',2),'[',']') as Session
Resident table1;

Maybe also helpful:

Uploading a Sample

hope this helps

regards

Marco