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: 
Not applicable

Load .Txt file and get the name of Dimensions and Values

Hi Community:

I'm facing a loading problem, the thing is that I´m trying to load data from different .Txt files that contain informationf of stores, the problem is that this information is dinamicaly and the columns are not going to be the same everyday:

Example of Txt file:

C~4100~Country Code~ca~CLIMATE~cold~CORE VOLUME~5~PETITE VOLUME~5~LOU & GREY VOLUME~5~Refined~x~

C~4102~Country Code~ca~CLIMATE~cold~CORE VOLUME~5~PETITE VOLUME~5~LOU & GREY VOLUME~3~Refined~~

C~4104~Country Code~ca~CLIMATE~cold~CORE VOLUME~1~PETITE VOLUME~1~LOU & GREY VOLUME~1~Refined~~

C~4103~Country Code~ca~CLIMATE~cold~CORE VOLUME~3~PETITE VOLUME~5~LOU & GREY VOLUME~5~Refined~~

Its important to mention that this order is not going to be the same, maybe tomorrow the txt file is not going to have Cimate~cold for example.

As you can see in the exapmple the delimiter is '~' but @1 is the C and then @2 is  the code of the store then @3 is Country Code and @4 is the cod of Ca  that belongs to Country Code.

The rule would be thw non pairs columns are the "name of the dimension" and the pair columns are the values of the "Dimensions"

Does anyone know how can I achive this dinamic load?

I'm ataching one txt file as an example.

Thanks and Regards

1 Solution

Accepted Solutions
MarcoWedel

Hi,

one way to store the result into one qvd could be:

tabTemp:

Generic

LOAD RecNo() as ID,

    SubField([@1:n],'~',IterNo()*2-1) as ColNam,

    SubField([@1:n],'~',IterNo()*2) as ColVal

FROM [https://community.qlik.com/servlet/JiveServlet/download/1185992-259376/C-File%20LOFT%20Trait%20List%...] (fix, codepage is 1252)

While IterNo() <= Ceil((SubStringCount([@1:n],'~')+1)/2);

table1:

LOAD FieldValue('ID',RecNo()) as ID

AutoGenerate FieldValueCount('ID');

FOR i = NoOfTables()-1 to 0 STEP -1

  LET vTable=TableName($(i));

  IF WildMatch('$(vTable)', 'tabTemp.*') THEN

  LEFT JOIN (table1) LOAD * RESIDENT [$(vTable)];

  DROP TABLE [$(vTable)];

  ENDIF

NEXT i

STORE table1 into table1.qvd (qvd);

see also:

Use cases for Generic Load | Qlikview Cookbook

Qlik Design Blog

But although recombining the tables created by the generic load in this case seemed necessary to store the qvd, these posts might also be interesting:

Should We Stop Worrying and Love the Synthetic Key?

Synthetic Keys

hope this helps

regards

Marco

View solution in original post

10 Replies
MK_QSL
MVP
MVP

~4100~Country Code~ca~CLIMATE~cold~CORE VOLUME~5~PETITE VOLUME~5~LOU & GREY VOLUME~5~Refined~x~Casual~x~Assets~~Top 50 Collection~x~New Collection~x~Early Spring List~~Tier 1 Shoes~~Tier 2 Shoes~x~Beach~~Cashmere~~Wear to Work~~L&G expanded ~~Petite Full Assortment~~Real Estate Stores~~Denim Destruction~~Vacation~

What is the output you are expecting for above line?

MarcoWedel

Hi,

I guess one solution might be:

QlikCommunity_Thread_244824_Pic1.JPG

Generic

LOAD RecNo() as ID,

    SubField([@1:n],'~',IterNo()*2-1) as ColNam,

    SubField([@1:n],'~',IterNo()*2) as ColVal

FROM [https://community.qlik.com/servlet/JiveServlet/download/1185992-259376/C-File%20LOFT%20Trait%20List%...] (fix, codepage is 1252)

While IterNo() <= Ceil((SubStringCount([@1:n],'~')+1)/2);

hope this helps

regards

Marco

MK_QSL
MVP
MVP

Very nice... marcowedel

How you got below link to load...

[https://community.qlik.com/servlet/JiveServlet/download/1185992-259376/C-File%20LOFT%20Trait%20List%...] (fix, codepage is 1252)

Not applicable
Author

Hi Marco first of all thanks for responding so quickly and in the eve of new year, I really apreciate it.., I think this is great, I only want to ask you how can I store this in only one QVD, the purpose of this extraction and transformation is to generate this QVD and use it as latter in the app with the relation of the store #  that in this case is column name C.

MarcoWedel

Hi,

I used the link to the OP attachment in the File Wizard selecting file type "fixed record":

QlikCommunity_Thread_244824_Pic2.JPG

QlikCommunity_Thread_244824_Pic3.JPG

QlikCommunity_Thread_244824_Pic4.JPG

EDIT: replaced wrong screenshots

regards

Marco

MarcoWedel

Hi,

one way to store the result into one qvd could be:

tabTemp:

Generic

LOAD RecNo() as ID,

    SubField([@1:n],'~',IterNo()*2-1) as ColNam,

    SubField([@1:n],'~',IterNo()*2) as ColVal

FROM [https://community.qlik.com/servlet/JiveServlet/download/1185992-259376/C-File%20LOFT%20Trait%20List%...] (fix, codepage is 1252)

While IterNo() <= Ceil((SubStringCount([@1:n],'~')+1)/2);

table1:

LOAD FieldValue('ID',RecNo()) as ID

AutoGenerate FieldValueCount('ID');

FOR i = NoOfTables()-1 to 0 STEP -1

  LET vTable=TableName($(i));

  IF WildMatch('$(vTable)', 'tabTemp.*') THEN

  LEFT JOIN (table1) LOAD * RESIDENT [$(vTable)];

  DROP TABLE [$(vTable)];

  ENDIF

NEXT i

STORE table1 into table1.qvd (qvd);

see also:

Use cases for Generic Load | Qlikview Cookbook

Qlik Design Blog

But although recombining the tables created by the generic load in this case seemed necessary to store the qvd, these posts might also be interesting:

Should We Stop Worrying and Love the Synthetic Key?

Synthetic Keys

hope this helps

regards

Marco

Not applicable
Author

Thanks Marco it works perfectly, but now I´m trying with a different file and I am receiving this Script Error:

Script error.JPG

I think It´s becaus in the file in a couple of lines we don´t have the Climate as the other lines:

C~0591~Country Code~us~~~Proforma~1646~HP~2   ***********

C~1046~Country Code~us~~~Proforma~1800~HP~2   ***********

C~1078~Country Code~us~CLIMATE~cold~Proforma~1800~HP~2

MarcoWedel

Hi,

you're right.

If there is no "Climate" field name defined in some lines of your file, then this error will occur.

You can skip those empty values like this:

tabTemp:

Generic

LOAD *

Where Len(ColNam);

LOAD RecNo() as ID,

    SubField([@1:n],'~',IterNo()*2-1) as ColNam,

    SubField([@1:n],'~',IterNo()*2) as ColVal

FROM [C-File LOFT Trait List - CA.TXT] (fix, codepage is 1252)

While IterNo() <= Ceil((SubStringCount([@1:n],'~')+1)/2);

...

hope this helps

regards

Marco

Not applicable
Author

Thank you very much Marco you are the best I´am truly appreciate the help... 

Marco Wedel &lt;span class=&quot;icon-status-icon icon-mvp&quot; title=&quot;Mvp&quot;&gt;&lt;/span&gt; escribió:

Hi,

you're right.

If there is no "Climate" field name defined in some lines of your file, then this error will occur.

You can skip those empty values like this:

  1. tabTemp: 
  2. Generic 
  3. LOAD * 
  4. Where Len(ColNam); 
  5. LOAD RecNo() as ID, 
  6.     SubField([@1:n],'~',IterNo()*2-1) as ColNam, 
  7.     SubField([@1:n],'~',IterNo()*2) as ColVal 
  8. FROM [C-File LOFT Trait List - CA.TXT] (fix, codepage is 1252) 
  9. While IterNo() <= Ceil((SubStringCount([@1:n],'~')+1)/2); 
  10. ... 

hope this helps

regards

Marco