Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ecabanas
Creator II
Creator II

Problem with crosstable

Hi,

I'm triying to transform this table

customerSalesIdBrandRevenues
Customer1PV001X10 €
Customer1PV001Y10 €
Customer1PV001Z10 €
Customer2PV002X10 €
Customer2PV002Y10 €
Customer2PV002Z10 €
Customer1PV003X10 €
Customer1PV003Y10 €
Customer2PV004Y10 €
Customer2PV004Z10 €

 

into this

CustomerSalesIDBrandXBrandYBrandZ
Customer1PV001101010
Customer2PV002101010
Customer3PV0031010 
Customer4PV004  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

Labels (1)
7 Replies
Stoyan_Terziev
Partner - Creator III
Partner - Creator III

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

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
Partner - Creator III
Partner - Creator III

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

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

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
Partner - Creator III
Partner - Creator III

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

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'