Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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?
hope this helps
regards
Marco
~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?
Hi,
I guess one solution might be:
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
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)
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.
Hi,
I used the link to the OP attachment in the File Wizard selecting file type "fixed record":
EDIT: replaced wrong screenshots
regards
Marco
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
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?
hope this helps
regards
Marco
Thanks Marco it works perfectly, but now I´m trying with a different file and I am receiving this Script Error:
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
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
Thank you very much Marco you are the best I´am truly appreciate the help...
Marco Wedel <span class="icon-status-icon icon-mvp" title="Mvp"></span> 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:
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