Re: Problem with crosstable - Qlik Community

ecabanas · ‎2019-11-28

Hi,

I'm triying to transform this table

customer	SalesId	Brand	Revenues
Customer1	PV001	X	10 €
Customer1	PV001	Y	10 €
Customer1	PV001	Z	10 €
Customer2	PV002	X	10 €
Customer2	PV002	Y	10 €
Customer2	PV002	Z	10 €
Customer1	PV003	X	10 €
Customer1	PV003	Y	10 €
Customer2	PV004	Y	10 €
Customer2	PV004	Z	10 €

into this

Customer	SalesID	BrandX	BrandY	BrandZ
Customer1	PV001	10	10	10
Customer2	PV002	10	10	10
Customer3	PV003	10	10
Customer4	PV004			10

I'm using a Crosstable function but I did not reach the second table as I want. Any idea of how to do it? I'm completely lost

Cross754:
CrossTable (Brand,Revenues)
LOAD SL_Customer,SalesId resident SaleslineCounter2;

Thank's

Stoyan_Terziev · ‎2019-11-28

Hi Ecabanas,

You are looking for the opposite of a crosstable. It's called Generic Load.

Here is a nice article about it:
https://community.qlik.com/t5/Qlik-Design-Blog/The-Generic-Load/ba-p/1473470

I hope that helps!

Regards,

S.T.

ecabanas · ‎2019-11-28

Hi @Stoyan_Terziev

Thank's for your help, But i don't want thousands of tables (it's a nightmare 😉 ), I would like to do only one table...how do I join all tables created?

Thank's

Stoyan_Terziev · ‎2019-11-28

Hi @ecabanas ,

There is a section in the article dedicated to this part:

   Set vListOfTables = ;

   For vTableNo = 0 to NoOfTables()

      Let vTableName = TableName($(vTableNo)) ;

      If Subfield(vTableName,'.',1)='GenericLabel' Then

         Let vListOfTables = vListOfTables & If(Len(vListOfTables)>0,',') & Chr(39) & vTableName & Chr(39) ;

      End If

   Next vTableNo

 

   CombinedGenericTable:

   Load distinct Key From GenericDB;

 

   For each vTableName in $(vListOfTables)

      Left Join (CombinedGenericTable) Load * Resident [$(vTableName)];

      Drop Table [$(vTableName)];

   Next vTableName

That's the general script, but you need to adapt it to your case.

This will basically join all those tables back together and form one single big table as you wish to see it.

Kind regards,

S.T.

ecabanas · ‎2019-11-28

Hi @Stoyan_Terziev

I did it and adpted in my script but I have an error

SaleslineCounter:
LOAD
floor(monthend(CreatedatSalesLine)) as created_at,
    SalesId_ as SalesId_Segmentation ,
    ItemId,
    Catlevel2,
    SL_Brand,
    SL_Customer,
    Import_Brut
FROM [lib://folder_QVD (ulabox_ecabanas)/SalesLine_DAMM.qvd]
(qvd);

SaleslineCounter2:
Load SL_Customer,SalesId_Segmentation, SL_Brand,1 as countbrand754 resident SaleslineCounter where Catlevel2 ='754';

Cross754:
Generic LOAD SalesId_Segmentation, SL_Brand,countbrand754  resident SaleslineCounter2;


Set vListOfTables = ;

   For vTableNo = 0 to NoOfTables()

      Let vTableName = TableName($(vTableNo)) ;

      If Subfield(vTableName,'.',1)='Cross754' Then

         Let vListOfTables = vListOfTables & If(Len(vListOfTables)>0,',') & Chr(39) & vTableName & Chr(39) ;

      End If

   Next vTableNo

 

   CombinedGenericTable:

   Load distinct SalesId_Segmentation From Cross754;

 

   For each vTableName in $(vListOfTables)

      Left Join (CombinedGenericTable) Load * Resident [$(vTableName)];

      Drop Table [$(vTableName)];

   Next vTableName

drop table SaleslineCounter2;
drop table SaleslineCounter;

error:

11:54:56
No qualified path for file: ***: CombinedGenericTable: Load distinct SalesId_Segmentation From Cross754

Thank's for your patience

ecabanas · ‎2019-11-28

Hi @Stoyan_Terziev

I did it as the document said, but as I don't control the routine I have an error that I did not have any idea how to fix it.

This is my code:

SaleslineCounter:
LOAD
floor(monthend(CreatedatSalesLine)) as created_at,
    SalesId_ as SalesId_Segmentation ,
    ItemId,
    Catlevel2,
    SL_Brand,
    SL_Customer,
    Import_Brut
FROM [lib://folder_QVD (ulabox_ecabanas)/SalesLine_DAMM.qvd]
(qvd);

SaleslineCounter2:
Load SL_Customer,SalesId_Segmentation, SL_Brand,1 as countbrand754 resident SaleslineCounter where Catlevel2 ='754';



Cross754:
Generic LOAD SalesId_Segmentation, SL_Brand,countbrand754  resident SaleslineCounter2;

drop table SaleslineCounter2;
drop table SaleslineCounter;

Set vListOfTables = ;

   For vTableNo = 0 to NoOfTables()

      Let vTableName = TableName($(vTableNo)) ;

      If Subfield(vTableName,'.',1)='Cross754' Then

         Let vListOfTables = vListOfTables & If(Len(vListOfTables)>0,',') & Chr(39) & vTableName & Chr(39) ;

      End If

   Next vTableNo

 

CombinedGenericTable:

Load distinct SalesId_Segmentation From Cross754;

 

   For each vTableName in $(vListOfTables)

      Left Join (CombinedGenericTable) Load * Resident [$(vTableName)];

      Drop Table [$(vTableName)];

   Next vTableName

But the error is :

Se ha producido el siguiente error:
No qualified path for file: ***
El error se ha producido aquí:
CombinedGenericTable:

Load distinct SalesId_Segmentation From Cross754

Thank's for your enormous patience

Best

Eduard

Stoyan_Terziev · ‎2019-11-28

Hi,

The issue was that you use Resident load at the wrong place. I fixed this part:

CombinedGenericTable:
Load distinct SalesId_Segmentation RESIDENT SaleslineCounter2;
drop table SaleslineCounter2;

SaleslineCounter:
LOAD
floor(monthend(CreatedatSalesLine)) as created_at,
    SalesId_ as SalesId_Segmentation ,
    ItemId,
    Catlevel2,
    SL_Brand,
    SL_Customer,
    Import_Brut
;
LOAD * INLINE [
SalesId_,ItemId,Catlevel2, SL_Brand,SL_Customer,Import_Brut,CreatedatSalesLine
1,A,754,SL_BR1,Cust_1,Import_A,11/28/2019
1,A,754,SL_BR2,Cust_2,Import_B,11/27/2019
];

SaleslineCounter2:
NoConcatenate
Load SL_Customer,SalesId_Segmentation, SL_Brand,1 as countbrand754 resident SaleslineCounter where Catlevel2 ='754';


Cross754:
Generic LOAD SalesId_Segmentation, SL_Brand,countbrand754  resident SaleslineCounter2;

drop table SaleslineCounter;

Set vListOfTables = ;

   For vTableNo = 0 to NoOfTables()

      Let vTableName = TableName($(vTableNo)) ;

      If Subfield(vTableName,'.',1)='Cross754' Then

         Let vListOfTables = vListOfTables & If(Len(vListOfTables)>0,',') & Chr(39) & vTableName & Chr(39) ;

      End If

   Next vTableNo


CombinedGenericTable:
Load distinct SalesId_Segmentation RESIDENT SaleslineCounter2;

 
drop table SaleslineCounter2;

   For each vTableName in $(vListOfTables)

      Left Join (CombinedGenericTable) Load * Resident [$(vTableName)];

      Drop Table [$(vTableName)];

   Next vTableName

ecabanas · ‎2019-11-28

Hi,

Thank's for your help...but still with problems, I received this error, and no idea what it means

14:46:18
Se ha producido el siguiente error:
14:46:18
Unexpected token: 's', expected one of: ',', 'OPERATOR_PLUS', 'OPERATOR_MINUS', 'OPERATOR_MULTIPLICATION', 'OPERATOR_DIVISION', 'OPERATOR_STRING_CONCAT', 'like', ...
14:46:18
---
14:46:18
El error se ha producido aquí:
14:46:18
For each vTableName in 'Cross754.Voll Damm','Cross754.Estrella Damm','Cross754.Estrella de Levante','Cross754.Estrella Galicia','Cross754.Cruzcampo','Cross754.Turia','Cross754.San Miguel','Cross754.Moritz','Cross754.Free Damm','Cross754.INEDIT','Cross754.Rosita','Cross754.Buckler','Cross754.Paulaner','Cross754.A.K. Damm','Cross754.Heineken','Cross754.Amstel','Cross754.Daura','Cross754.Alhambra','Cross754.Keler','Cross754.Guinness','Cross754.BrewDog','Cross754.Cervesa del Montseny','Cross754.Franziskaner','Cross754.Mongozo','Cross754.Mahou','Cross754.Carlsberg','Cross754.La Pirata','Cross754.Stone Brewing CO','Cross754.Domus','Cross754.Flying Monkeys','Cross754.Bbp','Cross754.Damm Lemon','Cross754.Menduiña','Cross754.Barcelona Beer Company','Cross754.Skol','Cross754.Xibeca','Cross754.Budweiser','Cross754.Flensburger','Cross754.Weihenstephaner','Cross754.De Ranke','Cross754.Saaz','Cross754.Beck'>>>>>>s<<<<<<','Cross754.Brugse Zot','Cross754.O'hara's','Cross754.Orval','Cross754.Brouwerij Van Steenberge','Cross754.Coronita','Cross754.Delirium','Cross754.St. Bernardus','Cross754.Trappistes Rochefort','Cross754.Triple Karmeliet','Cross754.Val-Dieu','Cross754.Brooklyn','Cross754.Straffe Hendrik','Cross754.Liefmans','Cross754.Porterhouse','Cross754.Caracole','Cross754.La Brava','Cross754.Desperados','Cross754.Brouwerij Verhaeghe','Cross754.Vic Brewery','Cross754.Brunehaut','Cross754.Petràs','Cross754.Gourmet (Marca Blanca)','Cross754.Brutus','Cross754.Ratpenat Cervesers','Cross754.Mixta','Cross754.Brasserie Lefebvre','Cross754.Brouwerij Huyghe','Cross754.Benediktiner','Cross754.Murphy','Cross754.Daura Märzen','Cross754.Samuel Smith','Cross754.Timmermans','Cross754.Cerveza Mandril','Cross754.Zywiec','Cross754.Affligem','Cross754.John Smiths','Cross754.Victoria','Cross754.Bock Damm','Cross754.Birra Moretti','Cross754.Sapporo','Cross754.Brewery Bosteels','Cross754.Floris','Cross754.Celis White','Cross754.Judas','Cross754.Milana','Cross754.El Oso y el Cuervo','Cross754.Sol','Cross754.L'Anjub','Cross754.Brasserie de l'Abbaye des Rocs','Cross754.Kriek','Cross754.Art Cervesers','Cross754.Kasteelbier','Cross754.Maisel's Weisse','Cross754.Cubanero','Cross754.Carling','Cross754.Asahi Breweries','Cross754.Kirin','Cross754.Brasserie Du Bocq','Cross754.Biir','Cross754.Basqueland Brewing Project','Cross754.B. Plankstetten','Cross754.Riedenburger','Cross754.Brouwerij Malheur','Cross754.Iki','Cross754.Popaire','Cross754.Collective Arts Brewing Limited','Cross754.Oskar Blues Brewery','Cross754.Nickel Brook Brewing Co','Cross754.Mikkeller','Cross754.Brasserie Dunham','Cross754.Chimay','Cross754.Brouwerij der Trappisten van Westmalle','Cross754.Shepherd Neame','Cross754.Brasserie d’Achouffe','Cross754.Camerons Brewery','Cross754.Yellow Cab','Cross754.Charles Wells','Cross754.Leffe','Cross754.Brouwerij Oud Beersel','Cross754.Lindemans','Cross754.Brouwerij Rodenbach','Cross754.Krombacher','Cross754.Cannabis','Cross754.Robinsons Brewery','Cross754.Stella','Cross754.Lowenbrau','Cross754.Guineu','Cross754.Tiger','Cross754.Les Brasseurs De Gayant','Cross754.In Peccatum Craft Beer','Cross754.Radeberger','Cross754.Birra & Blues Artesanal Craft','Cross754.Boston Beer Company','Cross754.Brouwerij der Trappistenabdij De Achelse Kluis','Cross754.Brasserie Dubuisson','Cross754.La Calavera Brewing COOP.','Cross754.Harviestoun','Cross754.Glutenberg Brewing CO','Cross754.Les Trois Mousquetaires','Cross754.Cobeer','Cross754.Reptilian','Cross754.Wierquer','Cross754.Birrificio Baladin','Cross754.DouGall's','Cross754.Newcastle','Cross754.La Virgen','Cross754.Cerveses Ponent','Cross754.Ambar','Cross754.Brasserie De Block','Cross754.OOB Brewery','Cross754.Schofferhofer','Cross754.Althaia Artesana','Cross754.Hecatombe Brewing','Cross754.New Belgium Brewing','Cross754.Illa','Cross754.Wychwood','Cross754.Michelada Malinche','Cross754.Brasserie St-Feuillien','Cross754.John Martin','Cross754.Thornbridge Brewery','Cross754.Sierra Nevada Brewing Co.','Cross754.Mont des Cats','Cross754.Brasserie D'Achouffe','Cross754.Brauerei Schloss Eggenberg','Cross754.Dieu du Ciel','Cross754.Amura','Cross754.Brouwerij Van Honsebrouck','Cross754.Unibroue','Cross754.Sawdust City Brewing Co.','Cross754.Varios','Cross754.Brasserie Dieu Du Ciel','Cross754.Central City','Cross754.Complot IPA','Cross754.Ambar IPA','Cross754.Ambar Roja','Cross754.Malquerida','Cross754.Adlerbrau','Cross754.Brouwerij Het Anker','Cross754.Tibidabo Brewing','Cross754.Almogàver','Cross754.St. Sylvestre','Cross754.Cerveza Madera','Cross754.Pilsner Urquell','Cross754.Clausthaler','Cross754.Grimbergen','Cross754.Praga','Cross754.Kagua','Cross754.Shandy'

Problem with crosstable

General Question