Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
FEDE
Contributor II
Contributor II

DOUBLE CROSS TABLE

Hi everybody,
I have an excel made in this way:

shop owner Is the shop in city center? note Shop size note1 Are there enough sale item note2
a john Y Near the church Small Little shop with lot of other activities near there Yes NA
b james Y In the main Square Medium The shop is the biggeest in the Square No They sold everything on weekend
c Juliette N In a very crowded neighborhood Large Inside shopping center Yes NA

 

the note refer to question "Is the shop in city center?"; note1 refer to "Shop Size", and note2 refer to "Are there enough sale item".
I want to obtain a table like that:

shop owner question answer note
a john Is the shop in city center? Y Near the church
b james Is the shop in city center? Y In the main Square
c Juliette Is the shop in city center? N In a very crowded neighborhood
a john Shop size Small Little shop with lot of other activities near there
b james Shop size Medium The shop is the biggeest in the Square
c Juliette Shop size Large Inside shopping center
a john Are there enough sale item Yes NA
b james Are there enough sale item No They sold everything on weekend
c Juliette Are there enough sale item Yes NA


Is possible to obtain it without using ALIAS? Maybe using the cell of excel like "A";"B";"C"....

Thank you so much
BYE 🙂




Labels (1)
1 Solution

Accepted Solutions
BenjaminT
Partner - Creator
Partner - Creator

Assuming you're familiar with crosstable... you can do three separate crosstables like this:

shop_data:
crosstable(question, answer,3)
LOAD
    shop,
    owner,
    note,
    "Is the shop in city center?"
FROM [lib://DataFiles/shop_notes.xlsx]
(ooxml, embedded labels, table is Sheet1);


crosstable(question, answer,3)
LOAD
    shop,
    owner,
    note1 as note,
    "Shop size"
FROM [lib://DataFiles/shop_notes.xlsx]
(ooxml, embedded labels, table is Sheet1);


crosstable(question, answer,3)
LOAD
    shop,
    owner,
    note2 as note,
    "Are there enough sale item"
FROM [lib://DataFiles/shop_notes.xlsx]
(ooxml, embedded labels, table is Sheet1);

 

View solution in original post

1 Reply
BenjaminT
Partner - Creator
Partner - Creator

Assuming you're familiar with crosstable... you can do three separate crosstables like this:

shop_data:
crosstable(question, answer,3)
LOAD
    shop,
    owner,
    note,
    "Is the shop in city center?"
FROM [lib://DataFiles/shop_notes.xlsx]
(ooxml, embedded labels, table is Sheet1);


crosstable(question, answer,3)
LOAD
    shop,
    owner,
    note1 as note,
    "Shop size"
FROM [lib://DataFiles/shop_notes.xlsx]
(ooxml, embedded labels, table is Sheet1);


crosstable(question, answer,3)
LOAD
    shop,
    owner,
    note2 as note,
    "Are there enough sale item"
FROM [lib://DataFiles/shop_notes.xlsx]
(ooxml, embedded labels, table is Sheet1);