topic Re: Loading a comma seperated value file in QlikView

Loading a comma seperated value file

sibrulotte — Tue, 22 Jul 2014 14:06:10 GMT

Hi,

I am mesmerised (seems that it doesn't take me alot) by this:

Here is my load statement created from the wizard:

LOAD [Num compte ],

     [Date fact ],

     [Num tél mo],

     [Nom                 ],

     [Prénom       ],

     [Num référ           ],

     [Abonné de groupe              ],

     [Catégorie      ],

     [Sous-catégorie ],

     [Total-redr ],

     [Tot fr mens],

     [Fr option ],

     Durtemps,

     [Fr temps   ],

     [Fr données ],

     [Fr itinér ],

     [Fr int itin],

     [Fr d'inter ],

     [Autres fr ],

     [Esc total ],

     [Tot fr cour],

     MessTxt,

     [Tot MessTxt],

     [Dur don ],

     Durinter,

     [Dur itin],

     Intdndur,

     Fraisintdon,

     [Vol TP ],

     [Utilis TP ],

     [TPS        ],

     [TVH        ],

     [TVH ÎPÉ Tel],

     [TVHO Tel   ],

     [TVHBC Tel ],

     [TVO        ],

     [TVQ - téléc],

     [TVQ - autre],

     [TVP ÎPÉ    ],

     [TVP CB     ],

     [TVP - Sask ],

     [TVP - Man ],

     [Tx autr pay],

     [Du 10-4P],

     [Frais 10-4P],

     [Du 10-4G],

     [Frais 10-4G],

     [It 10-4 ],

     [Fr it 10-4 ],

     [Min jour],

     [Min W/E ],

     [Min soir],

     MinTrAnt,

     MinAdmTr,

     MinAdmUt,

     MinExpFct,

     TotMinTr,

     [Min à part ],

     [Min part ut],

     [Mo à part ],

     [Mo part ut ],

     [Évén à part],

     [Év part ut ],

     [App à part ],

     [App part ut]

FROM

[MY FILE PATH\0101352803_07012014_MOB_TEST]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is 1 lines);

My file is attached to the post.

Nothing else in the QVW.

I get an error message saying the first field not found...

Can someone try it?

Re: Loading a comma seperated value file

ashfaq_haseeb — Tue, 22 Jul 2014 14:10:45 GMT

Hi,

Try below

Directory;
LOAD [Acct Nbr], 
     [Invc Date], 
     [Mobile Nbr], 
     Surname, 
     [Given Name], 
     [Ref Nbr], 
     [Group Subscriber], 
     Category, 
     SubCategory, 
     [Adj App Ttl], 
     [Mth Chg Ttl], 
     [Ftr Chg Ttl], 
     [Air Dur], 
     [Airtime Chg], 
     [Data Chg], 
     [Roamer Chg], 
     [Roamer LD], 
     [LD Chg], 
     [Other Chgs], 
     [Disc Ttl], 
     [Ttl Charges], 
     [Txt Msg], 
     [Txt Msg Amt], 
     [Data Dur], 
     [LD Dur], 
     [Roam Dur], 
     Rmdatdur, 
     [Rm datachrg], 
     PktDtVol, 
     [Pkt Dat Amt], 
     GST, 
     HST, 
     [HST-PEI Tel], 
     [HST-ON Tel], 
     [HST-BC Tel], 
     ORST, 
     [QST - Telec], 
     [QST - Other], 
     [P.E.I. PST], 
     [BC PST], 
     Sask, 
     Manitoba, 
     [Foreign tax], 
     [10-4P Dr], 
     [10-4 P Amt], 
     [10-4G Dr], 
     [10-4 G Amt], 
     [10-4 Rmr], 
     [10-4 Rm Amt], 
     [Min day], 
     [Min W/E], 
     [Min nght], 
     RLprevmin, 
     RLminelig, 
     RLminused, 
     RLBexpmin, 
     RLtotal, 
     [Grp Sh Min], 
     [Sh Min Used], 
     [Grp Sh MB], 
     [Sh MB Used], 
     [Grp Sh Evts], 
     [Sh Evt Used], 
     [Grp Sh Call], 
     [Sh Call Use]
FROM
[0101352803_07012014_MOB_TEST.txt]
(txt, codepage is 1252, embedded labels, delimiter is ',', msq, filters(
Remove(Row, RowCnd(CellValue, 1, StrCnd(null)))
));

Regards

ASHFAQ

Re: Loading a comma seperated value file

jonathandienst — Tue, 22 Jul 2014 14:13:08 GMT

You need to take the trailing spaces out of the fieldnames.

[Num compte ] -> [Num compte],

HTH

Jonathan

Re: Loading a comma seperated value file

Anonymous — Tue, 22 Jul 2014 14:17:35 GMT

Ort maybe do it like this ?

This ignores the first row and assumes the second row contains the labels.

It all depends on what you wish to acheive.

Directory;

LOAD [Num compte ,Date fact ,Num tél mo,Nom ,Prénom ,Num référ ,Abonné de groupe ,Catégorie ,Sous-catégorie ,Total-redr ,Tot fr mens,Fr option ,Durtemps,Fr temps ,Fr données ,Fr itinér ,Fr int itin,Fr d'inter ,Autres fr ,Esc total ,Tot fr cour,MessTxt,Tot MessTxt,Dur don ,Durinter,Dur itin,Intdndur,Fraisintdon,Vol TP ,Utilis TP ,TPS ,TVH ,TVH ÎPÉ Tel,TVHO Tel ,TVHBC Tel ,TVO ,TVQ - téléc,TVQ - autre,TVP ÎPÉ ,TVP CB ,TVP - Sask ,TVP - Man ,Tx autr pay,Du 10-4P,Frais 10-4P,Du 10-4G,Frais 10-4G,It 10-4 ,Fr it 10-4 ,Min jour,Min W/E ,Min soir,MinTrAnt,MinAdmTr,MinAdmUt,MinExpFct,TotMinTr,Min à part ,Min part ut,Mo à part ,Mo part ut ,Évén à part,Év part ut ,App à part ,App part ut]

FROM

[0101352803_07012014_MOB_TEST.txt]

(txt, codepage is 1252, embedded labels, delimiter is ';', msq, header is 1 lines);